Find integer at end of a string. There must be a better solution ?

Question

Find integer at end of a string. There must be a better solution ?

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Lowell (7/26/2013)
Cadavre I love your new signature with the nolock pointers! just noticed it today.

I added it after the last time I had to dig them all out 🙂

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

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'

)

select * ,

coalesce( TRY_CONVERT(int,

nullif(SUBSTRING(x, 1 + nullif(CHARINDEX('-',x), 0), LEN(x)),

'')),

-1)

from tmp

LEN(x) - charindex can be reduced to LEN(x), since you want the rest of the string. I removed the outer CASE and replaced it with two NULLIF. One to capture that therte are no hyphen at all, and one to capture that there are only spaces after the hyphen.

There was a question about multiple hyphens. My assumptions from the original post is that the integer should be from the hyphen to the end of the string, so Abc-2-3 should return -1.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Dwain Camps SSC Guru Points: 86978 More actions · Answer 3

Cadavre - I love the name you gave mine: "COOL MATHS/"

I suspect it might have a bug though where the string ends like -$ (might show 0).

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Thorkil Johansen Old Hand Points: 311 More actions · Answer 4

Nice one... 🙂

I have the assumption that only one hyphen will be in the string, but if the case was

xyz-1-2.3-2

then 2 would be the right answer...

To sharpen a little: "The integer to the right of the last hyphen in the string"

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 5

The use of IsNumeric in onne of the solutions serves as a reminder that there are a whole lot of things that look like numbers to SQLServer. Here's some updated test data to see what I mean...

with tmp as

(

SELECT x = 'some text-1'

UNION ALL SELECT x = 'some text-123'

UNION ALL SELECT x = 'some text-123.4'

UNION ALL SELECT x = 'some text 123'

UNION ALL SELECT x = 'some text-'

UNION ALL SELECT x = 'some text- ' --Added this for possible CHAR values

UNION ALL SELECT x = 'some text- 123 ' --Added "stray bullet"

UNION ALL SELECT x = 'some text-123 456' --Added "stray bullet"

UNION ALL SELECT x = 'some text-123d4' --Added "stray bullet"

UNION ALL SELECT x = 'some text-123e4' --Added "stray bullet"

UNION ALL SELECT x = 'some text-$1,234' --Added "stray bullet"

UNION ALL SELECT x = 'some text-1,234' --Added "stray bullet"

UNION ALL SELECT x = 'some text-0x1234,' --Added "stray bullet"

UNION ALL SELECT x = 'some text'

UNION ALL SELECT x = 'some text-xyz'

)

... put your code here ...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 6

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

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Thorkil Johansen Old Hand Points: 311 More actions · Answer 7

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

TomThomson SSC Guru Points: 104773 More actions · Answer 8

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 9

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

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Thorkil Johansen Old Hand Points: 311 More actions · Answer 10

Thanx for a nice answer...

And thanx for a nice blog as well 🙂

Hejsa TreKroner... 🙂

/T

Find integer at end of a string. There *must* be a better solution ?

Find integer at end of a string. There must be a better solution ?