SQL Syntax

SQL Server 2008.
Whats the best way for me to get the number size at the end of these strings and deal with "Invalid length parameter passed to the LEFT or SUBSTRING function." error.

Sample strings
1451/2S-931501-MGY-41 R    - Return 41
1450/1-302100-BLK-50 XL     - Return 50

Thanks for your help
LVL 1
johnnyactionAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

knightEknightCommented:
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) ) ) )

0
knightEknightCommented:
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
0
knightEknightCommented:
same thing, but uglier because I do it in one step... also I created a separate result variable:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

cyberkiwiCommented:
Sample

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

Open in new window

0
cyberkiwiCommented:
It looks extremely complicated, but it will extract the last sequential block of digits from ANY string without fail.
0
Patrick MatthewsCommented:
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.
0
knightEknightCommented:
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)

0
knightEknightCommented:
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
0
cyberkiwiCommented:
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
0
cyberkiwiCommented:
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

Open 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

Open in new window

0
SharathData EngineerCommented:
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
*/

Open in new window

0
johnnyactionAuthor Commented:
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
0
knightEknightCommented:
>> oops, thanks Patrick! :)

cw, that was me!  :D

I like your approach - always better to be as generic as possible.
0
cyberkiwiCommented:
double oops.. Thanks knightEknight
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.