Professional Web Applications Themes

Strip Leading Characters ?? - Microsoft SQL / MS SQL Server

I'd like to strip various leading characters from a registration number. for example MA1234 = 1234 987651 = 987651 Q10001 = 10001 The code below works but it's gotta be inefficient(and I have to account of all lengths). I thought I could use a WHILE loop that loops through the length of each value checking if it's numeric or not. Something like .... SELECT Code = CASE WHILE X <> Len(Code) IF isnumeric(right(Code,x)) = 1 right(code,x) BREAK ELSE SET x = x + 1 END ...But no matter how variations of this I try it won't work. I can get ...

  1. #1

    Default Strip Leading Characters ??

    I'd like to strip various leading characters from a
    registration number.
    for example
    MA1234 = 1234
    987651 = 987651
    Q10001 = 10001

    The code below works but it's gotta be inefficient(and I
    have to account of all lengths). I thought I could use a
    WHILE loop that loops through the length of each value
    checking if it's numeric or not.
    Something like ....

    SELECT Code =
    CASE
    WHILE X <> Len(Code)
    IF isnumeric(right(Code,x)) = 1
    right(code,x)
    BREAK
    ELSE
    SET x = x + 1
    END

    ...But no matter how variations of this I try it won't
    work. I can get a much larger cursor based SP working
    along these lines but I don't understand why this Set-
    Based code does not work, Any comments appreciated.


    SELECT code =
    CASE
    WHEN isnumeric(right(Code,8)) = 1 THEN right(code,8)
    WHEN isnumeric(right(code,7)) = 1 THEN right(code,7)
    WHEN isnumeric(right(code,6)) = 1 THEN right(code,6)
    WHEN isnumeric(right(code,5)) = 1 THEN right(code,5)
    WHEN isnumeric(right(code,4)) = 1 THEN right(code,4)
    WHEN isnumeric(right(code,3)) = 1 THEN right(code,3)
    ELSE
    null
    END
    FROM Table
    Mike Guest

  2. #2

    Default Re: Strip Leading Characters ??

    You could try something like:

    select right(code,Len(code)-patindex('%[1-9]%',code)+1)


    "Mike" <michael.Shapleskiwipa.org.nz> wrote in message
    news:063801c34128$e328b920$a501280aphx.gbl...
    > I'd like to strip various leading characters from a
    > registration number.
    > for example
    > MA1234 = 1234
    > 987651 = 987651
    > Q10001 = 10001
    >
    > The code below works but it's gotta be inefficient(and I
    > have to account of all lengths). I thought I could use a
    > WHILE loop that loops through the length of each value
    > checking if it's numeric or not.
    > Something like ....
    >
    > SELECT Code =
    > CASE
    > WHILE X <> Len(Code)
    > IF isnumeric(right(Code,x)) = 1
    > right(code,x)
    > BREAK
    > ELSE
    > SET x = x + 1
    > END
    >
    > ..But no matter how variations of this I try it won't
    > work. I can get a much larger cursor based SP working
    > along these lines but I don't understand why this Set-
    > Based code does not work, Any comments appreciated.
    >
    >
    > SELECT code =
    > CASE
    > WHEN isnumeric(right(Code,8)) = 1 THEN right(code,8)
    > WHEN isnumeric(right(code,7)) = 1 THEN right(code,7)
    > WHEN isnumeric(right(code,6)) = 1 THEN right(code,6)
    > WHEN isnumeric(right(code,5)) = 1 THEN right(code,5)
    > WHEN isnumeric(right(code,4)) = 1 THEN right(code,4)
    > WHEN isnumeric(right(code,3)) = 1 THEN right(code,3)
    > ELSE
    > null
    > END
    > FROM Table

    jeff Guest

  3. #3

    Default Re: Strip Leading Characters ??

    Cheers Jeff that worked a treat, I'll have to read up on
    PATINDEX. I can't help but wonder if a WHILE loop would
    work without using a CURSOR? Some more research I guess.
    Thanks again.

    >-----Original Message-----
    >You could try something like:
    >
    >select right(code,Len(code)-patindex('%[1-9]%',code)+1)
    >
    >
    >"Mike" <michael.Shapleskiwipa.org.nz> wrote in message
    >news:063801c34128$e328b920$a501280aphx.gbl...
    >> I'd like to strip various leading characters from a
    >> registration number.
    >> for example
    >> MA1234 = 1234
    >> 987651 = 987651
    >> Q10001 = 10001
    >>
    >> The code below works but it's gotta be inefficient(and I
    >> have to account of all lengths). I thought I could use a
    >> WHILE loop that loops through the length of each value
    >> checking if it's numeric or not.
    >> Something like ....
    >>
    >> SELECT Code =
    >> CASE
    >> WHILE X <> Len(Code)
    >> IF isnumeric(right(Code,x)) = 1
    >> right(code,x)
    >> BREAK
    >> ELSE
    >> SET x = x + 1
    >> END
    >>
    >> ..But no matter how variations of this I try it won't
    >> work. I can get a much larger cursor based SP working
    >> along these lines but I don't understand why this Set-
    >> Based code does not work, Any comments appreciated.
    >>
    >>
    >> SELECT code =
    >> CASE
    >> WHEN isnumeric(right(Code,8)) = 1 THEN right(code,8)
    >> WHEN isnumeric(right(code,7)) = 1 THEN right(code,7)
    >> WHEN isnumeric(right(code,6)) = 1 THEN right(code,6)
    >> WHEN isnumeric(right(code,5)) = 1 THEN right(code,5)
    >> WHEN isnumeric(right(code,4)) = 1 THEN right(code,4)
    >> WHEN isnumeric(right(code,3)) = 1 THEN right(code,3)
    >> ELSE
    >> null
    >> END
    >> FROM Table
    >
    >
    >.
    >
    Mike Guest

  4. #4

    Default Re: Strip Leading Characters ??

    try with this function

    create function str2num (input varchar(20))
    Returns int as
    Begin
    declare no int, output varchar(20)
    select no = 1, output = ''
    while no<=len(input)
    Begin
    IF isnumeric(substring(input,no,1))=1
    Begin
    set output = substring(input,no, 20)
    break
    End
    set no = no + 1
    End
    Return output
    End

    GO

    select dbo.str2num(field_name) as myval from mytable name
    or
    select dbo.str2num('abcd123456') as myval

    HTH
    Falik


    "Mike" <michael.Shapleskiwipa.org.nz> wrote in message
    news:063801c34128$e328b920$a501280aphx.gbl...
    > I'd like to strip various leading characters from a
    > registration number.
    > for example
    > MA1234 = 1234
    > 987651 = 987651
    > Q10001 = 10001
    >
    > The code below works but it's gotta be inefficient(and I
    > have to account of all lengths). I thought I could use a
    > WHILE loop that loops through the length of each value
    > checking if it's numeric or not.
    > Something like ....
    >
    > SELECT Code =
    > CASE
    > WHILE X <> Len(Code)
    > IF isnumeric(right(Code,x)) = 1
    > right(code,x)
    > BREAK
    > ELSE
    > SET x = x + 1
    > END
    >
    > ..But no matter how variations of this I try it won't
    > work. I can get a much larger cursor based SP working
    > along these lines but I don't understand why this Set-
    > Based code does not work, Any comments appreciated.
    >
    >
    > SELECT code =
    > CASE
    > WHEN isnumeric(right(Code,8)) = 1 THEN right(code,8)
    > WHEN isnumeric(right(code,7)) = 1 THEN right(code,7)
    > WHEN isnumeric(right(code,6)) = 1 THEN right(code,6)
    > WHEN isnumeric(right(code,5)) = 1 THEN right(code,5)
    > WHEN isnumeric(right(code,4)) = 1 THEN right(code,4)
    > WHEN isnumeric(right(code,3)) = 1 THEN right(code,3)
    > ELSE
    > null
    > END
    > FROM Table

    Falik Sher Guest

  5. #5

    Default Re: Strip Leading Characters ??

    why limit yourself to leading characters. How bout a function that checks
    each character and skips it if it is not numeric.
    But some characters like $ i think are numeric so watch out



    "Mike" <michael.Shapleskiwipa.org.nz> wrote in message
    news:063801c34128$e328b920$a501280aphx.gbl...
    > I'd like to strip various leading characters from a
    > registration number.
    > for example
    > MA1234 = 1234
    > 987651 = 987651
    > Q10001 = 10001
    >
    > The code below works but it's gotta be inefficient(and I
    > have to account of all lengths). I thought I could use a
    > WHILE loop that loops through the length of each value
    > checking if it's numeric or not.
    > Something like ....
    >
    > SELECT Code =
    > CASE
    > WHILE X <> Len(Code)
    > IF isnumeric(right(Code,x)) = 1
    > right(code,x)
    > BREAK
    > ELSE
    > SET x = x + 1
    > END
    >
    > ..But no matter how variations of this I try it won't
    > work. I can get a much larger cursor based SP working
    > along these lines but I don't understand why this Set-
    > Based code does not work, Any comments appreciated.
    >
    >
    > SELECT code =
    > CASE
    > WHEN isnumeric(right(Code,8)) = 1 THEN right(code,8)
    > WHEN isnumeric(right(code,7)) = 1 THEN right(code,7)
    > WHEN isnumeric(right(code,6)) = 1 THEN right(code,6)
    > WHEN isnumeric(right(code,5)) = 1 THEN right(code,5)
    > WHEN isnumeric(right(code,4)) = 1 THEN right(code,4)
    > WHEN isnumeric(right(code,3)) = 1 THEN right(code,3)
    > ELSE
    > null
    > END
    > FROM Table

    Guest

Similar Threads

  1. How to strip of characters when long text is displayed?
    By WestSide in forum Macromedia ColdFusion
    Replies: 3
    Last Post: March 11th, 03:06 PM
  2. Replies: 1
    Last Post: August 12th, 03:28 PM
  3. Replies: 7
    Last Post: July 23rd, 04:53 PM
  4. Replies: 3
    Last Post: July 18th, 06:29 PM
  5. Replies: 3
    Last Post: July 9th, 10:25 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