August 31, 2009 at 7:40 am
Hi All,
If I have the value of "A2000" and "B2D400", how do I extract the value "2000" from the first value and "400" from the second value?
Thanks.
August 31, 2009 at 7:49 am
This should work.
DECLARE @TestSeq VARCHAR(20)
SET @TestSeq = 'A2000'
SELECT RIGHT(@TestSeq, PATINDEX('%[^0-9]%',REVERSE(@TestSeq))-1)
August 31, 2009 at 11:07 am
Hi Garadin,
That worked. Thank you for your time. You saved my life.
August 31, 2009 at 2:21 pm
ramadesai108 (8/31/2009)
Hi Garadin,That worked. Thank you for your time. You saved my life.
Glad to help. It's not every day I get to save a life! 😉
Also, here's a little explanation in case anyone reading it doesn't quite understand what it's doing:
SELECT RIGHT(@TestSeq, PATINDEX('%[^0-9]%',REVERSE(@TestSeq))-1)
REVERSE takes a string and reverses it. IE. REVERSE('SomeString') = 'gnirtSemoS'.
I use this to go through the string starting from the right, as we're only interested in anything after the last non-numeric character.
PATINDEX('%[^0-9]%' returns the numeric position of the first non-numeric (the ^ symbol = not, [0-9] is a range including 0123456789) character, and because of the reverse, goes from right to left. Once you find the non-numeric character, you want everything before it, so subtract 1.
This kind of thing *could* be stored at the table level using persisted computed columns depending on what you need it for.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply