• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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
0
johnnyaction
Asked:
johnnyaction
2 Solutions
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now