SQL Syntax

I don't know if this is the "best" way, but it seems to do the trick:


declare @str varchar(max)
select  @str = '1450/1-302100-BLK-50 XL'
select @str = REVERSE(@str)
select REVERSE( substring( @str, charIndex(' ',(@str)), charIndex('-',(@str)) - charIndex(' ',(@str) ) ) )

I should have re-assigned it back to the string like this:

declare @str varchar(max)
select  @str = '1450/1-302100-BLK-50 XL'
select @str = REVERSE(@str)
select @str = REVERSE( substring( @str, charIndex(' ',(@str)), charIndex('-',(@str)) - charIndex(' ',(@str) ) ) )
select @str as result

It looks extremely complicated, but it will extract the last sequential block of digits from ANY string without fail.

Pure genius, ck!

Of course, if SQL Server gave us native RegExp support (what the heck, Oracle and MySQL do) it wouldn't be that hard.  I know we could ude RegExp in a CLR function, but that makes my head hurt.

cyberwiki - great work!  ...except fyi the results need to be re-reversed.  As it is now:


  '1451/2S-931501-MGY-41 R'  result:  14  (instead of 41)
  '1450/1-302100-BLK-50 XL'   result:  05  (instead of 50)

for the given strings, bounded by '-' on the left and a space on the right, this will do it inline:

select REVERSE( substring( REVERSE(myColumn), charIndex(' ',(REVERSE(myColumn))),
   charIndex('-',(REVERSE(myColumn))) - charIndex(' ',(REVERSE(myColumn)) ) ) )
from myTable

oops, thanks Patrick! :)

I know you left this open so I can post the correct version, double thanks.

select
      CASE WHEN PATINDEX('%[^0-9]%', STUFF(reverse(col),1,PATINDEX('%[0-9]%', REVERSE(col))-1,'')) = 0
      THEN reverse(STUFF(reverse(col),1,PATINDEX('%[0-9]%', REVERSE(col))-1,''))
      ELSE REVERSE(STUFF(STUFF(reverse(col),1,PATINDEX('%[0-9]%', REVERSE(col))-1,''),
              PATINDEX('%[^0-9]%', STUFF(reverse(col),1,PATINDEX('%[0-9]%', REVERSE(col))-1,'')), 1000, ''))
      END
from @t

Or use a function that is less cryptic

create function dbo.getLastDigits(@s nvarchar(max)) returns nvarchar(max)
as
begin
-- any longer than 10 characters is too big for an int anyway
-- even at 10, it may overflow an int
declare @i int, @j int, @tmp varchar(10)
set @i = len(@s)
while @i > 0 and not substring(@s,@i,1) like '[0-9]' set @i=@i-1
set @j = @i -1
while @j > 0 and substring(@s,@j,1) like '[0-9]' set @j=@j-1
return case when @j=-1 then null else substring(@s, @j+1, @i-@j) end
end

Select allOpen in new window

The return type is declared as nvarchar(max), so that it can handle long digit sequences (as string) and also preserve leading 0's.  To make ints out of the return, just multiply by 1, e.g.

declare @t table (col varchar(max))
insert @t select '1451/2S-931501-MGY-41 R'
insert @t select '1450/1-302100-BLK-50 XL'
insert @t select ''
insert @t select '1450/1-302100-BLK-50'
insert @t select 'no number at all'
insert @t select null
insert @t select '019 leading number'

select 1*dbo.getLastDigits(col) from @t

Select allOpen in new window

Do you always have data like XXX-XXX-XXX-XXX ? i.e. not more than 3 hyphens? If so, you can also try like this.

declare @t table (col varchar(max))
insert @t select '1451/2S-931501-MGY-41 R'
insert @t select '1450/1-302100-BLK-50 XL'
insert @t select ''
insert @t select '1450/1-302100-BLK-50'
insert @t select 'no number at all'
insert @t select null
insert @t select '019 leading number'

SELECT col, 
       LEFT(PARSENAME(REPLACE(CASE 
                                WHEN col LIKE '%-%' THEN col 
                                ELSE NULL 
                              END,'-','.'),1),ISNULL(NULLIF(CHARINDEX(' ',PARSENAME(REPLACE(CASE 
                                                                                              WHEN col LIKE '%-%' THEN col 
                                                                                              ELSE NULL
                                                                                            END,'-','.'),1)), 
                                                            0),LEN(col))) AS ExtractedPart 
  FROM @t
/*
col	ExtractedPart
1451/2S-931501-MGY-41 R	41 
1450/1-302100-BLK-50 XL	50 
	NULL
1450/1-302100-BLK-50	50
no number at all	NULL
NULL	NULL
019 leading number	NULL
*/

Select allOpen in new window

I actually had something very similar to these posts. I split the points because of the time you both put in and I could use either one. This is a great website. Thanks for your help

>> oops, thanks Patrick! :)

cw, that was me!  :D

I like your approach - always better to be as generic as possible.

double oops.. Thanks knightEknight