# Function to find position of the last numeric character inconsistent - Microsoft SQL / MS SQL Server

SQL Server 200 The following function is intended to help split the numeric beginning of any word that consists of numeric and alphanumeric characters (we use it for sorting addresses in number order), by returning the last position (from the begining of the word) that is numeric. Apologies for the poor explanation! For example 1A returns 1, 1Flat returns 1 However : '1A' returns 1 - Correct '1A1' returns 1 - Correct '1D' returns 1 - Correct '1D1' returns 3 - INCORRECT Can anyone explain this and suggest how it may be resolved? I did not write the function and ...

1. ## Function to find position of the last numeric character inconsistent

SQL Server 200

The following function is intended to help split the numeric beginning of
any word that consists of numeric and alphanumeric characters (we use it for
sorting addresses in number order), by returning the last position (from the
begining of the word) that is numeric. Apologies for the poor explanation!

For example 1A returns 1, 1Flat returns 1

However :

'1A' returns 1 - Correct
'1A1' returns 1 - Correct
'1D' returns 1 - Correct
'1D1' returns 3 - INCORRECT

Can anyone explain this and suggest how it may be resolved? I did not write
the function and am a relative beginner so please try and keep it simple.

Thanks for any help

CREATE FUNCTION fnSplitNumberAtBeginning (theword varchar(200))
RETURNS int
AS
BEGIN
declare thecharpos int
if (isnumeric(theword) > 0)
set thecharpos = len(theword) -- no need to split
else
begin
declare position int
set position = 1
set thecharpos = 0
while position <= LEN(theword)
begin
if (isnumeric(left(theword, position))> 0)
begin
set position = position + 1 -- look at next character
set thecharpos = position - 1
end
else
set position = len(theword) + 1
end
end
RETURN thecharpos
END

2. ## Re: Function to find position of the last numeric character inconsistent

Jacco,

Thanks very much.

Excuse my ignorance, but how can this be put in a user defined function as
we currently use?

"Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
news:u1uIHMhQDHA.3088TK2MSFTNGP10.phx.gbl...
> You can do this a lot simpler than using a user defined function:
>
> SELECT PATINDEX('%[^0-9]%', '12450Hello') -1
>
> This returns the position before (-1) the first position (PATINDEX) that
is
> not (^) 0-9.
>
> The original function fails because isnumeric() treats 1d1 as a numeric
> value, it is the scientific notation for 1 to the power of 1 in some
> systems/languages.
>
>
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Eurostop Ltd.
>
>
> "Adrian" <NoSpamhotmail.com> wrote in message
> news:OgeJx3gQDHA.2312TK2MSFTNGP12.phx.gbl...
> > SQL Server 200
> >
> > The following function is intended to help split the numeric beginning
of
> > any word that consists of numeric and alphanumeric characters (we use it
> for
> > sorting addresses in number order), by returning the last position (from
> the
> > begining of the word) that is numeric. Apologies for the poor
explanation!
> >
> > For example 1A returns 1, 1Flat returns 1
> >
> > However :
> >
> > '1A' returns 1 - Correct
> > '1A1' returns 1 - Correct
> > '1D' returns 1 - Correct
> > '1D1' returns 3 - INCORRECT
> >
> > Can anyone explain this and suggest how it may be resolved? I did not
> write
> > the function and am a relative beginner so please try and keep it
simple.
> >
> > Thanks for any help
> >
> >
> >
> > CREATE FUNCTION fnSplitNumberAtBeginning (theword varchar(200))
> > RETURNS int
> > AS
> > BEGIN
> > declare thecharpos int
> > if (isnumeric(theword) > 0)
> > set thecharpos = len(theword) -- no need to split
> > else
> > begin
> > declare position int
> > set position = 1
> > set thecharpos = 0
> > while position <= LEN(theword)
> > begin
> > if (isnumeric(left(theword, position))> 0)
> > begin
> > set position = position + 1 -- look at next character
> > set thecharpos = position - 1
> > end
> > else
> > set position = len(theword) + 1
> > end
> > end
> > RETURN thecharpos
> > END
> >
> >
> >
>
>

3. ## Re: Function to find position of the last numeric character inconsistent

You don't have to use a user defined function at all. UDF's can be a lot
slower than 'plain' SQL, so it is best not to use them if you can avoid it.
Just replace all occurences of fnSplitNumberAtBeginning (<column name or
variable>) with PATINDEX('%[^0-9]%', <column name or variable>) -1.

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Eurostop Ltd.

news:#7k7CQhQDHA.1988TK2MSFTNGP12.phx.gbl...
> Jacco,
>
> Thanks very much.
>
> Excuse my ignorance, but how can this be put in a user defined function as
> we currently use?
>
>
> "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
> news:u1uIHMhQDHA.3088TK2MSFTNGP10.phx.gbl...
> > You can do this a lot simpler than using a user defined function:
> >
> > SELECT PATINDEX('%[^0-9]%', '12450Hello') -1
> >
> > This returns the position before (-1) the first position (PATINDEX) that
> is
> > not (^) 0-9.
> >
> > The original function fails because isnumeric() treats 1d1 as a numeric
> > value, it is the scientific notation for 1 to the power of 1 in some
> > systems/languages.
> >
> >
> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Eurostop Ltd.
> >
> >
> > "Adrian" <NoSpamhotmail.com> wrote in message
> > news:OgeJx3gQDHA.2312TK2MSFTNGP12.phx.gbl...
> > > SQL Server 200
> > >
> > > The following function is intended to help split the numeric beginning
> of
> > > any word that consists of numeric and alphanumeric characters (we use
it
> > for
> > > sorting addresses in number order), by returning the last position
(from
> > the
> > > begining of the word) that is numeric. Apologies for the poor
> explanation!
> > >
> > > For example 1A returns 1, 1Flat returns 1
> > >
> > > However :
> > >
> > > '1A' returns 1 - Correct
> > > '1A1' returns 1 - Correct
> > > '1D' returns 1 - Correct
> > > '1D1' returns 3 - INCORRECT
> > >
> > > Can anyone explain this and suggest how it may be resolved? I did not
> > write
> > > the function and am a relative beginner so please try and keep it
> simple.
> > >
> > > Thanks for any help
> > >
> > >
> > >
> > > CREATE FUNCTION fnSplitNumberAtBeginning (theword varchar(200))
> > > RETURNS int
> > > AS
> > > BEGIN
> > > declare thecharpos int
> > > if (isnumeric(theword) > 0)
> > > set thecharpos = len(theword) -- no need to split
> > > else
> > > begin
> > > declare position int
> > > set position = 1
> > > set thecharpos = 0
> > > while position <= LEN(theword)
> > > begin
> > > if (isnumeric(left(theword, position))> 0)
> > > begin
> > > set position = position + 1 -- look at next character
> > > set thecharpos = position - 1
> > > end
> > > else
> > > set position = len(theword) + 1
> > > end
> > > end
> > > RETURN thecharpos
> > > END
> > >
> > >
> > >
> >
> >
>
>

Jacco Schalkwijk Guest

4. ## Re: Function to find position of the last numeric character inconsistent

Jacco,

Thanks, but if I just replace the function as you suggested I get errors.

I suspect this is because if the variable is just numeric it is
returning -1, whereas in the function I am using it would return the length
of the numeric variable, as below:
> > > > if (isnumeric(theword) > 0)
> > > > set thecharpos = len(theword) -- no need to split
> > > > else
I am a bit out of my depth here and would welcome any help.

"Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
news:Oh89CVhQDHA.1684TK2MSFTNGP12.phx.gbl...
> You don't have to use a user defined function at all. UDF's can be a lot
> slower than 'plain' SQL, so it is best not to use them if you can avoid
it.
> Just replace all occurences of fnSplitNumberAtBeginning (<column name or
> variable>) with PATINDEX('%[^0-9]%', <column name or variable>) -1.
>
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Eurostop Ltd.
>
>
> "Adrian" <NoSpamhotmail.com> wrote in message
> news:#7k7CQhQDHA.1988TK2MSFTNGP12.phx.gbl...
> > Jacco,
> >
> > Thanks very much.
> >
> > Excuse my ignorance, but how can this be put in a user defined function
as
> > we currently use?
> >
> >
> > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
> > news:u1uIHMhQDHA.3088TK2MSFTNGP10.phx.gbl...
> > > You can do this a lot simpler than using a user defined function:
> > >
> > > SELECT PATINDEX('%[^0-9]%', '12450Hello') -1
> > >
> > > This returns the position before (-1) the first position (PATINDEX)
that
> > is
> > > not (^) 0-9.
> > >
> > > The original function fails because isnumeric() treats 1d1 as a
numeric
> > > value, it is the scientific notation for 1 to the power of 1 in some
> > > systems/languages.
> > >
> > >
> > > --
> > > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > > Eurostop Ltd.
> > >
> > >
> > > "Adrian" <NoSpamhotmail.com> wrote in message
> > > news:OgeJx3gQDHA.2312TK2MSFTNGP12.phx.gbl...
> > > > SQL Server 200
> > > >
> > > > The following function is intended to help split the numeric
beginning
> > of
> > > > any word that consists of numeric and alphanumeric characters (we
use
> it
> > > for
> > > > sorting addresses in number order), by returning the last position
> (from
> > > the
> > > > begining of the word) that is numeric. Apologies for the poor
> > explanation!
> > > >
> > > > For example 1A returns 1, 1Flat returns 1
> > > >
> > > > However :
> > > >
> > > > '1A' returns 1 - Correct
> > > > '1A1' returns 1 - Correct
> > > > '1D' returns 1 - Correct
> > > > '1D1' returns 3 - INCORRECT
> > > >
> > > > Can anyone explain this and suggest how it may be resolved? I did
not
> > > write
> > > > the function and am a relative beginner so please try and keep it
> > simple.
> > > >
> > > > Thanks for any help
> > > >
> > > >
> > > >
> > > > CREATE FUNCTION fnSplitNumberAtBeginning (theword varchar(200))
> > > > RETURNS int
> > > > AS
> > > > BEGIN
> > > > declare thecharpos int
> > > > if (isnumeric(theword) > 0)
> > > > set thecharpos = len(theword) -- no need to split
> > > > else
> > > > begin
> > > > declare position int
> > > > set position = 1
> > > > set thecharpos = 0
> > > > while position <= LEN(theword)
> > > > begin
> > > > if (isnumeric(left(theword, position))> 0)
> > > > begin
> > > > set position = position + 1 -- look at next character
> > > > set thecharpos = position - 1
> > > > end
> > > > else
> > > > set position = len(theword) + 1
> > > > end
> > > > end
> > > > RETURN thecharpos
> > > > END
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>