Native string manipulation functions for SQL Server

Deep in the bowels of the invaluable SQLServerPedia site there are countless custom functions that can be used in most any context. I recently dug up a slew of string manipulation functions. While these might normally be used after data has been returned from a query, they are also useful to perform on the database server itself.

Here's a rundown of the functions:

Count occurrences of one string within another. This uses a WHILE loop, the CHARINDEX and SUBSTRING functions to return a text string along the lines of "The inner string is contained in the outer string x times." You'll need to edit the function to just return a simple number, which you could do by replacing line 26 as follows:

SELECT CAST(@n AS VARCHAR(4))

Note that this also item has also not been wrapped up as a formal function. It's just a free-floating piece of procedural code,so you could do the above and swap the above SELECT for an actual RETURN.

Get a substring that begins and ends at specified points. A wrapper for the SUBSTRING function that lets you quickly chomp out part of another string by providing startpoints and endpoints. It's a close cousin to the replace a substring function, which allows you to swap out part of one string for another by providing a startpoint and endpoint in the string to be manipulated.

Reverse a string. This lets you flip the order of a whole string or a subsection within it.

Trim patterns of a string. This one shaves out every instance of a given character from within a string.

Word wrap a string. This one, probably the most useful of the bunch, lets you take a string and word-wrap it at a specified line length. If you are publishing data that may be formatted to an existing line length regardless of the frontend, you could use this to pre-format the data and store a copy of it for fast retrieval.

Note that this splits lines at spaces, but not at hyphens, slashes, etc., so any line more than the specified number of characters but with no spaces in that range will just be forcibly split at the maximum length. Also note that there is a typo on line 30, where a greater-than sign has been mistakenly rendered in the code as & GT; probably due to an HTML misconversion.

I should mention a couple of other things. First, as noted above, not all of these are wrapped up as formal functions, so you might need to do some work to package them a little more properly for your own ends.

Second, the maximum length of the input parameters vary widely between each function; if you want to stay away from any weird string-truncation issues, you can just cast the input as VARCHAR(MAX) -- in SQL Server 2005 and up -- or VARCHAR(8000), depending on the likely usage for the function and how stringently the input is going to be screened.

ABOUT THE AUTHOR:
Serdar Yegulalp has been writing about computers and information technology for more than 15 years for a variety of publications, including InformationWeek and Windows Magazine.