Strip Leading Characters ?? -- sql server programming

Mike
7/2/2003 11:03:56 PM

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

jeff
7/2/2003 11:50:05 PM

You could try something like:

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

[quoted text, click to view]

Mike
7/3/2003 12:08:15 AM

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.

[quoted text, click to view]

fera NO[at]SPAM fro.com
7/3/2003 1:39:54 AM

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

[quoted text, click to view]

Falik Sher
7/3/2003 12:23:03 PM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Strip Leading Characters ??