Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Find integer at end of a string. There *must* be a better solution ? Expand / Collapse
Author
Message
Posted Saturday, July 27, 2013 2:56 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 803, Visits: 720
Thorkil Johansen (7/27/2013)To sharpen a little: "The integer to the right of the last hyphen in the string"


In such case...
with tmp as (
select x = 'some text-1'
union select x = 'some text-123'
union select x = 'some text-123.4'
union select x = 'some text 123'
union select x = 'some text-'
union select x = 'some text'
union select x = 'some text-xyz'
union select x = 'some text-2.2-44'
)
select * ,
coalesce( TRY_CONVERT(int,
nullif(SUBSTRING(x, len(x) - nullif(CHARINDEX('-', reverse(x)), 0) + 2, LEN(x)),
'')),
-1)
from tmp



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1478309
Posted Thursday, August 29, 2013 2:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 27, 2014 4:06 AM
Points: 11, Visits: 50
Hi

Allow me to wake this thread up again...

When using this:
select ISNULL(TRY_CONVERT(INT,SUBSTRING('Ejerskifte-45\Behandlet-45',
NULLIF(PATINDEX('%-[0-9]%','Ejerskifte-45\Behandlet-45'),0)
+ 1,LEN('Ejerskifte-45\Behandlet-45')) ),-1)

I get -1 I would like it to be 45
Post #1489611
Posted Thursday, August 29, 2013 7:31 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 8,844, Visits: 9,406
Thorkil Johansen (8/29/2013)
select ISNULL(TRY_CONVERT(INT,SUBSTRING('Ejerskifte-45\Behandlet-45',
NULLIF(PATINDEX('%-[0-9]%','Ejerskifte-45\Behandlet-45'),0)
+ 1,LEN('Ejerskifte-45\Behandlet-45')) ),-1)

I get -1 I would like it to be 45

The problem is that trying to convert the 26 character string '45\Behandlet-45' to INT delivers NULL. You need to hand the the right string to TRY_CONVERT. It isn't clear which of the two occurrences of '45'in the original string you want (or even whether it matters which you get - if the numeric is repeated twice in each sting you want to do this with, as it is in this example, it doesn't matter which string you get); if it's the first one, you can correct the length using another call of PATINDEX to find the first not --9 character after the start you've selected, and then doing some arithmetic; if you want something that's at the end of the string, there are suggestions earlier in this topic for finding the right start position and the length you use in the call to substring can be the length of the whole original string since you are going to the end.


Tom
Post #1489716
Posted Thursday, August 29, 2013 1:08 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 803, Visits: 720
The solution I posted works for this string as well:

with tmp as (
select x = 'some text-1'
union select x = 'some text-123'
union select x = 'some text-123.4'
union select x = 'some text 123'
union select x = 'some text-'
union select x = 'some text'
union select x = 'some text-xyz'
union select x = 'some text-2.2-44'
union select x = 'Ejerskifte-45\Behandlet-45'
)
select * ,
coalesce( TRY_CONVERT(int,
nullif(SUBSTRING(x, len(x) - nullif(CHARINDEX('-', reverse(x)), 0) + 2, LEN(x)),
'')),
-1)
from tmp



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1489854
Posted Thursday, August 29, 2013 3:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 27, 2014 4:06 AM
Points: 11, Visits: 50
Thanx for a nice answer...

And thanx for a nice blog as well

Hejsa TreKroner...

/T
Post #1489937
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse