SQL 2005+

SQL Server Miscellaneous String Functions (2)

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.

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]'