DECLARE @longString varchar(300) , @control varchar(10) , @test varchar(10) , @lastSpacePosition int , @negativeIndex int SELECT @longString = 'Four score and seven years ago our fathers brought forth on this continent a new nation, conceived in liberty, and dedicated to the proposition that all men are created equal' , @control = 'equal' -- Find the first space of the reversed string, assumes no trailing space -- and back off one SELECT @lastSpacePosition = CHARINDEX(' ', REVERSE(@longString),1) -1 -- Slice off the right N characters SELECT @test = RIGHT(@longString, @lastSpacePosition) IF (@test = @control) BEGIN PRINT '@billinkc is a genius! Mega-Upvote' END ELSE BEGIN PRINT 'Ack! Something went horribly wrong' END SELECT @negativeIndex = CHARINDEX(' ', @longString, -1) -- nothing up my sleeves , @test = NULL -- Slice off the right N characters SELECT @test = RIGHT(@longString, @lastSpacePosition) IF (@test = @control) BEGIN PRINT '@billinkc is a actually a tickturd' END ELSE BEGIN PRINT 'perhaps @billinkc is not that bad' END
A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.
Tuesday, August 16, 2011
TSQL: Find the last X in a string
A question came up about how do you find the last word in a sentence in SQL. My mind immediately went to the clever solution I used far too many times. The last instance of something becomes the first if you approach from the other end. Given the weaksauce string capabilities of TSQL, my solution was to reverse the string, find the first instance of whatever I was looking for and then slice that many characters off the end of the string. It was a good solution, it worked and it only took 2 executions to get it right as I typically forgot to back off one to get the correct starting position. I then saw someone using the MySQL method and they passed in a value of -1 to their find. I then began a feverish prayer of "that doesn't work in SQL Server, that doesn't work in SQL Server." You can run the script for yourself to see whether charindex works with negative values.