SQL 2005+

SQL Server Miscellaneous String Functions

The fiftieth part of the SQL Server Programming Fundamentals tutorial completes the review of the string functions provided by Transact-SQL. This article describes six miscellaneous functions not mentioned in the previous six articles in the series.

Miscellaneous Functions

This article describes the use of six miscellaneous string functions provided by Transact-SQL (T-SQL). These are the string functions that did not belong to any of the categories described in the previous six articles of the tutorial. These functions, like most others, can be used in queries and other T-SQL statements. In this article we will demonstrate their use through simple PRINT statements.

Space

The Space function provides a simple way to create a string containing up to 8,000 space characters. The function requires a single argument, which contains the number of spaces to return in a Char result. The function does not produce Unicode data. If you require a Unicode result, use the Replicate function described in the next section.

PRINT '"' + space(5) + '"'  -- "     "

Replicate

The Replicate function is similar to the Space function. However, rather than creating a series of spaces, Replicate can be used to generate a repeating string of any character or a group of characters. The function accepts two arguments. The first is the string to be duplicated and can be any character data type. The second parameter specifies how many times the string should be repeated.

PRINT replicate('-=',5)     -- "-=-=-=-=-="

NB: If the first parameter is not a VarChar(MAX) or NVarChar(MAX) then the result will be truncated if it exceeds 8,000 bytes in size.

Replace

The Replace function performs a simple find and replace operation upon a string. This function requires three parameters. The first is the string in which you wish to make replacements. The second is the string to find and the third is the replacement text. Every occurrence of the search string is replaced in the result, which will be a VarChar or NVarChar. Matches vary according to the collation in use. For example, a case-insensitive collation will allow case-insensitive matching but a case-sensitive collation will not match upper case to lower case letters.

PRINT replace('Hello [0], Goodbye [0]', '[0]', 'Jim');  -- Hello Jim, Goodbye Jim

Reverse

The Reverse function simply reverses the order of the characters in a string. The function requires an argument containing the text to be processed. The provided string must be able to be implicitly converted to a VarChar or NVarChar. The returned result will also be a VarChar or NVarChar.

PRINT reverse('psaWkcalB'); -- BlackWasp