Tuesday, December 04, 2007

A colleague is doing some data migration work, and had a string he needed to manipulate to get at the id. The string had been put together as a title, with spaces replaced with -, and an ID with the title and ID separated by a -, i.e. Software-Developer-123 where title is Software Developer and the ID is 123.

He'd written some code to manipulate this, but it was taking quite a while and wanted to know if there was a one line TSQL statement to do it.

After a bit of head scratching we came up with the following:

DECLARE @strTemp VARCHAR(100)
SET @strTemp = 'Software-Developer-123'
SELECT SUBSTRING(@strTemp, LEN(@strTemp)-(CHARINDEX('-',REVERSE(@strTemp)))+2, CHARINDEX('-',REVERSE(@strTemp))-1)

This returns 123, and so is a (long) one line fix to his problem. As there is no LastIndexOf or similar function, I've used the REVERSE function to enable us to find the last instance of - and then its just some manipulation using CHARINDEX and SUBSTRING to get at the ID. The use of the variable @strTemp was just so we could easily swap in different test data and so isn't of any real importance to the solution.

I really enjoy challenges like this and have emailed this on as a brainteaser to the rest of the development team.

Update:
@DECLARE @strTemp VARCHAR(100)
SET @strTemp = 'Software-Developer-123'

SELECT RIGHT(@strTemp, CHARINDEX('-',REVERSE(@strTemp))-1)
A lot cleaner :-) Thanks Alex.

Labels: SQL, SQLServer, SQLServer2005

// posted by Jane @ 5:33 PM   

Comments:

Post a Comment