Professional Web Applications Themes

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. #1

    Default 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

    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



    Adrian Guest

  2. #2

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

    Adrian

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

    Adrian Guest

  3. #3

    Default 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
    Database Administrator
    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?
    >
    > Adrian
    >
    > "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
    > > 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 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
    > > >
    > > > 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
    > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Jacco Schalkwijk Guest

  4. #4

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

    Adrian

    "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
    > Database Administrator
    > 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?
    > >
    > > Adrian
    > >
    > > "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
    > > > 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
    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
    > > > >
    > > > > 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
    > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Adrian Guest

Similar Threads

  1. Replies: 7
    Last Post: January 19th, 06:17 AM
  2. Find Position of control
    By Sundance Kid in forum ASP.NET Building Controls
    Replies: 0
    Last Post: January 21st, 02:25 PM
  3. Position of Character
    By Andre Hessler in forum Macromedia Flash Actionscript
    Replies: 2
    Last Post: January 20th, 11:25 AM
  4. Return a Character String-position through a function
    By J. Darrington in forum Microsoft Access
    Replies: 2
    Last Post: July 17th, 02:24 PM
  5. note 33756 added to function.is-numeric
    By guidoz@rack1.php.net in forum PHP Notes
    Replies: 0
    Last Post: July 7th, 03:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139