Little known Sql String functions

We are all familiar with SQL string functions like LEFT and SUBSTRING. Here are some of the lesser known Sql string functions:

PATINDEX lets you search a string for the first instance that matches a given pattern. The pattern must begin and end with % signs. So the following code selects all the last names in the table that contain the pattern 'ack'.

SELECT LastName FROM tblPersonnel
WHERE PATINDEX('%ack%', LastName) > 0

STUFF inserts a string into another string. It can delete a specified number of characters from the start position before inserting the string. Here is some code that starts at the second letter in the string, deletes the next two characters and then inserts the phrase 'ack'.

SELECT STUFF(LastName, 2, 2, 'ack') FROM tblPersonnel

SOUNDEX and DIFFERENCE can by used to compare how much two strings sound alike. SOUNDEX takes a string and returns a four-character that can be used by DIFFERENCE to determine if two strings sound alike. Here's and example:

SELECT SOUNDEX('Barnett') --> B653
SELECT LastName FROM tblPersonnel
WHERE DIFFERENCE('Barnett', LastName) < 4

Notice that I didn't have to use the SOUNDEX function in the difference parameters.


Here are two where I can't think of a reason to use:
REPLICATE returns a string that is composed by repeating a string for a set number of iterations. Heres a sample:

SELECT REPLICATE('A', 5) --> 'AAAAA'

REVERSE produces a mirrored image of the string. So if you want to return all the names in the table spelled backwards, you could use:

SELECT REVERSE(LastName) FROM tblPersonnel

Like I said, I can't think of any real world examples where those last two would be useful, but they are cool anyway. If you can come up with an example, let me know.