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

Question

Post reply

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

Thorkil Johansen

Old Hand

Points: 311
More actions
July 25, 2013 at 6:57 am

#280952

Hi
I want to find an integer after a hyphen in a string. If found, then use it. If not, then return -1
This is the best I could figure out:
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 *
,case when CHARINDEX('-',x) > 0 and LEN(x) > CHARINDEX('-',x) then
iif(TRY_CONVERT(int, SUBSTRING(x,1 + CHARINDEX('-',x),LEN(x)-CHARINDEX('-',x))) IS NULL,
-1,
SUBSTRING(x,1 + CHARINDEX('-',x),LEN(x)-CHARINDEX('-',x))
)
else -1 end as result
from tmp

Viewing 15 posts - 1 through 15 (of 24 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

...

select *, ISNULL(CAST(SUBSTRING(x,NULLIF(PATINDEX('%-[0-9]%',x),0) + 1,1) AS INT),-1)

from tmp

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

probably very similar, using reverse and patindex for the first non numeric character: from there, for the 1/-1, a case based on the len() would be what i would use.

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 x,REVERSE(SUBSTRING(REVERSE(x),0,PATINDEX('%[^0-9]%',REVERSE(x))))

FROM tmp

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 3

doh!, if it ends in [0-9] might be even easier?

SELECT *,

CASE

WHEN x LIKE '%[0-9]'

THEN 1

ELSE -1

END

from tmp

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Lowell (7/25/2013)
probably very similar, using reverse and patindex for the first non numeric character: from there, for the 1/-1, a case based on the len() would be what i would use.
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 x,REVERSE(SUBSTRING(REVERSE(x),0,PATINDEX('%[^0-9]%',REVERSE(x))))
FROM tmp

It returns 4 for "select x = 'some text-123.4'".

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Lowell (7/25/2013)
doh!, if it ends in [0-9] might be even easier?
SELECT *,
CASE
WHEN x LIKE '%[0-9]'
THEN 1
ELSE -1
END
from tmp

that will return 1 for union select x = 'some text-321'

I thought OP wanted first digit.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 6

thorkil (7/25/2013)
Hi
I want to find an integer after a hyphen in a string. If found, then use it. If not, then return -1

What do you want to do with 123.4?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

I've couldn't run OP code in SQL2008 as he is using 2012 function.

Now, I can see what is result of his query.

The following will produce the same:

select *

,ISNULL(TRY_CONVERT(INT,SUBSTRING(x,NULLIF(PATINDEX('%-[0-9]%',x),0) + 1,LEN(x)) ),-1)

from tmp

Looks like 123.4 is not an integer, so -1 should be returned.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 8

Eugene Elutin (7/25/2013)
Lowell (7/25/2013)
doh!, if it ends in [0-9] might be even easier?
SELECT *,
CASE
WHEN x LIKE '%[0-9]'
THEN 1
ELSE -1
END
from tmp
that will return 1 for union select x = 'some text-321'
I thought OP wanted first digit.

i might have misread this, i thought he wanted to return a 1 /-1 flag ,but also definitely misread integer to be is kinda sorta numeric, so i thought that one might hit the sweet spot.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

Someone said: Looks like 123.4 is not an integer, so -1 should be returned.

Yes, that's right

Thanx a lot for all the answers... It's evening here in Copenhagen. I will take a closer look tomorrow at work.

This is really a nice forum... 🙂

/T

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Thorkil Johansen (7/25/2013)
Someone said: Looks like 123.4 is not an integer, so -1 should be returned.
Yes, that's right

So if the string were xyz-1-2.3-2 what's the required answer?

2, because -2 are the last two characters? or -1 because -1-2.3-2 isn't an integer?

Tom

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

Hi Tom

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"

/T

Dwain Camps SSC Guru Points: 86948 More actions · Answer 12

My contribution ain't exactly pretty but might work for you:

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

CASE

WHEN EndStr IN (0,1) THEN -1

WHEN 0=ISNUMERIC(RIGHT(x, EndStr-1)) THEN -1

WHEN FLOOR(RIGHT(x, EndStr-1)) = RIGHT(x, EndStr-1)

THEN CAST(RIGHT(x, EndStr-1) AS INT)

ELSE -1

END

FROM tmp a

CROSS APPLY (SELECT CHARINDEX('-', REVERSE(x))) b(EndStr);

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

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

Forgive me for my simplistic approach 😀

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'

UNION ALL SELECT x = 'some text-xyz'

UNION ALL SELECT x = 'xyz-1-2.3-2'

)

SELECT *,

CASE WHEN x LIKE '%-[0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN REVERSE(SUBSTRING(REVERSE(x),0,CHARINDEX('-',REVERSE(x))))

ELSE - 1 END

FROM tmp;

--EDIT--

Quick performance check. Note, I borrowed Dwain's method for grabbing the last digits as I figured it'd be quicker than the reverse reverse as SQL is not good at string manipulation.

SET NOCOUNT ON;

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment;

END;

--1,000,000 Random rows of data

SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,

'some text'+ CASE WHEN (ABS(CHECKSUM(NEWID())) % 100) + 1 >= 50 THEN '-' ELSE '' END +

CASE WHEN (ABS(CHECKSUM(NEWID())) % 100) + 1 >= 50 AND CHARINDEX('.',CAST(RAND(CHECKSUM(NEWID())) * 100 AS VARCHAR(MAX))) > 3

THEN SUBSTRING(CAST(RAND(CHECKSUM(NEWID())) * 100 AS VARCHAR(MAX)),0,CHARINDEX('.',CAST(RAND(CHECKSUM(NEWID())) * 100 AS VARCHAR(MAX)))-1)

ELSE CAST(RAND(CHECKSUM(NEWID())) * 100 AS VARCHAR(MAX)) END AS x

INTO #testEnvironment

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

DECLARE @Loop CHAR(1) = '0', @HOLDER INT, @Duration CHAR(12), @StartTime DATETIME;

WHILE @Loop <= 5

BEGIN

RAISERROR('Loop: %s',0,1,@Loop) WITH NOWAIT;

RAISERROR('============',0,1) WITH NOWAIT;

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SELECT @StartTime = GETDATE();

SELECT @HOLDER =

CASE WHEN x LIKE '%-[0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

WHEN x LIKE '%-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' THEN RIGHT(x,CHARINDEX('-',REVERSE(x))-1)

ELSE - 1 END

FROM #testEnvironment;

SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);

RAISERROR('BIG CASE Duration: %s',0,1,@Duration) WITH NOWAIT;

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SELECT @StartTime = GETDATE();

SELECT @HOLDER =

CASE

WHEN EndStr IN (0,1) THEN -1

WHEN 0=ISNUMERIC(RIGHT(x, EndStr-1)) THEN -1

WHEN FLOOR(RIGHT(x, EndStr-1)) = RIGHT(x, EndStr-1)

THEN CAST(RIGHT(x, EndStr-1) AS INT)

ELSE -1

END

FROM #testEnvironment a

CROSS APPLY (SELECT CHARINDEX('-', REVERSE(x))) b(EndStr);

SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);

RAISERROR('COOL MATHS Duration: %s',0,1,@Duration) WITH NOWAIT;

SET @Loop = @Loop + 1;

END

Loop: 0

============

BIG CASE Duration: 00:00:51:533

COOL MATHS Duration: 00:00:12:057

Loop: 1

============

BIG CASE Duration: 00:00:52:107

COOL MATHS Duration: 00:00:11:967

Loop: 2

============

BIG CASE Duration: 00:00:53:087

COOL MATHS Duration: 00:00:12:240

Loop: 3

============

BIG CASE Duration: 00:00:52:077

COOL MATHS Duration: 00:00:12:100

Loop: 4

============

BIG CASE Duration: 00:00:51:477

COOL MATHS Duration: 00:00:11:970

Loop: 5

============

BIG CASE Duration: 00:00:52:023

COOL MATHS Duration: 00:00:12:010

Pretty consistent win for Dwain's solution over mine. The results show a similar pattern with an index (big case took around 50 seconds but the maths reduced down to around 7 seconds).

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/

Lowell SSC Guru Points: 323518 More actions · Answer 14

Cadavre I love your new signature with the nolock pointers! just noticed it today.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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 ?