BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
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

Str

The Str function is used to obtain a string representation of a floating-point number. There are three ways in which you can call this function. The first is with a single argument containing the Float to be converted. The resultant string is a ten character Char containing the value rounded to the nearest integer. If the numeric element of the string is less than ten characters in length, spaces are inserted to right-align the number. This is ideal for outputting columns of numeric data.

PRINT '"' + str(123.5) + '"'            -- "       124"

The default length for the returned string is ten characters. This can be modified by adding a second parameter containing the desired length. If the length is too small to show the integer part of the value, a series of asterisks is returned.

PRINT '"' + str(123.5, 5) + '"'         -- "  124"
PRINT str(123.5, 2)                     -- **

The third syntax for the Str function adds another integer parameter. This can be a number between zero and sixteen. It specifies the number of decimal places that you wish to include in the result. When specified, the input value is rounded to this number of decimal places. If necessary, trailing zeroes are added to the string to ensure the correct number of digits is present. Note that if the number contains more decimal places than can fit into the specified length, the resultant string will be truncated.

PRINT '"' + str(123.5, 10, 5) + '"'     -- " 123.50000"
PRINT '"' + str(123.5, 10, 15) + '"'    -- "123.500000"

QuoteName

The last function that we will examine is named, "QuoteName". This function is often used when generating SQL statements dynamically and where the table names, column names or other identifiers can include spaces and brackets. The function adds delimiters to the start and end of the string provided as an argument. Where the input string includes some reserved characters, these are escaped so that the returned string is a valid SQL Server identifier.

In the first sample there are no special characters in the string. In this case, brackets are added to the string to ensure that it is a valid identifier:

PRINT quotename('Table 1')          -- [Table 1]

The next sample shows the results when the input string contains brackets. In this case the closing bracket is replaced with a pair of brackets. This indicates that the first closing bracket is not simply the end of the identifier.

PRINT quotename('Table [1]')        -- [Table [1]]]

QuoteName supports three types of delimiter. The default is a pair of brackets. However, by providing an apostrophe (') or quote (") as the second parameter, you can elect to use alternatives. You can also specify explicitly that you wish to use brackets by providing either an opening or closing bracket character as the second argument. As you can see in the final sample script, changing the delimiters also affects which characters are escaped.

PRINT quotename('Table [1]', '[')   -- [Table [1]]]
PRINT quotename('Table [1]', ']')   -- [Table [1]]]
PRINT quotename('Table [1]', '"')   -- "Table [1]"
PRINT quotename('Table [1]', '''')  -- 'Table [1]'
2 December 2009