Jacco,

Thanks very much.

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

we currently use?

Adrian

"Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message

news:u1uIHMhQDHA.3088TK2MSFTNGP10.phx.gbl...is> 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) thatof> 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

> Database Administrator

> 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 beginningexplanation!> for> > any word that consists of numeric and alphanumeric characters (we use it> the> > sorting addresses in number order), by returning the last position (from> > begining of the word) that is numeric. Apologies for the poorsimple.> write> >

> > 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> > the function and am a relative beginner so please try and keep it>> >

> > Thanks for any help

> >

> > Adrian

> >

> >

> > 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

> >

> >

> >

>

## Bookmarks