SQL Server Queries – Stripping Extra Characters from the End of a String

clive All, Development, SQL, Tech Tip

Microsoft_SQL_ServerYesterday while implementing a particularly difficult SQL report query, I came across the following question:

Given a String ‘This is a test, this is a second test’, is there an easy method (preferably without using CLR) to search and replace the string to remove the word ‘test’ but only if it occurs as the last series of characters in a string?

The end result would be ‘This is a test, this is a second’ ?

 

Well, the answers to this question on the Microsoft MSDN forums miss a rather more elegant and straight forward approach. Simply this, pick a character sequence you’re comfortable you won’t find in the string, (in this example the won’t-find sequence is ‘##’) and do this to strip ‘test’ off the end:

REPLACE(REPLACE('##'+'This is a test, this is a second test'+'##', 'test##'), '##','')

… or this to strip it off the beginning if found…

REPLACE(REPLACE('##'+'This is a test, this is a second test'+'##', '##test'),'##','')

.. or this to strip it off the beginning or end…

REPLACE(REPLACE(REPLACE('##'+'This is a test, this is a second test'+'##', '##test', ''),'test##', ''),'##','')

Job done.