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

Cadavre

SSC-Forever

Points: 41690

July 26, 2013 at 3:30 am

#1636081

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/