SQL Server String Functions
SQL Server string functions are scalar functions that perform an operation on a string input value and return a string or numeric value.
ASCII Function
ASCII function returns the ASCII code value of the leftmost character of a string. This function is commonly used for comparing characters without knowing whether they're in upper or lower case. Upper case and lower case letters translate into different ASCII values, as the following example shows:
SELECT ASCII('A') AS UpperCase, ASCII('a') AS LowerCase
Results:
UpperCase LowerCase
-----------
65 97
UNICODE Function
UNICODE function works just like ASCII, except it accepts the Unicode character value as input. This could be useful if you're working with international character sets.
CHAR Function
The CHAR function does the opposite of ASCII - it returns an alphanumeric equivalent of an ASCII code. CHAR function accepts a single argument - a number between 0 and 255. It is often necessary to append a carriage return, line feed, or both to the query output. In such cases you can effectively use CHAR function, as follows:
SELECT 'My Output' + --Add three carriage returns and a line feed:
REPLICATE(CHAR(10), 3) + CHAR(13) + 'AnotherOutput'
Results:
My Output
AnotherOutput
NCHAR Function
NCHAR function works exactly like CHAR except it returns the Unicode character. This function is useful if you're working with large international character sets. Unlike CHAR function NCHAR can handle values between 0 and 65535.
QUOTENAME Function
The QUOTENAME function appends square brackets to the beginning and end of the string expression and thereby makes a string expression a valid SQL Server identifier. QUOTENAME function is useful when working with database object names that contain spaces and reserved words. Generally it's a bad idea to use reserved words, special characters and spaces inside your object names. However at times, such as when working with 3rd party software, you do not have a choice. The following example uses QUOTENAME function to create a valid identifier:
SELECT QUOTENAME('column name with spaces')
Results:
[column name with spaces]
Tags:
SQL Server string functions are scalar functions that perform an operation on a string input value and return a string or numeric value.
ASCII Function
ASCII function returns the ASCII code value of the leftmost character of a string. This function is commonly used for comparing characters without knowing whether they're in upper or lower case. Upper case and lower case letters translate into different ASCII values, as the following example shows:
SELECT ASCII('A') AS UpperCase, ASCII('a') AS LowerCase
Results:
UpperCase LowerCase
-----------
65 97
UNICODE Function
UNICODE function works just like ASCII, except it accepts the Unicode character value as input. This could be useful if you're working with international character sets.
CHAR Function
The CHAR function does the opposite of ASCII - it returns an alphanumeric equivalent of an ASCII code. CHAR function accepts a single argument - a number between 0 and 255. It is often necessary to append a carriage return, line feed, or both to the query output. In such cases you can effectively use CHAR function, as follows:
SELECT 'My Output' + --Add three carriage returns and a line feed:
REPLICATE(CHAR(10), 3) + CHAR(13) + 'AnotherOutput'
Results:
My Output
AnotherOutput
NCHAR Function
NCHAR function works exactly like CHAR except it returns the Unicode character. This function is useful if you're working with large international character sets. Unlike CHAR function NCHAR can handle values between 0 and 65535.
QUOTENAME Function
The QUOTENAME function appends square brackets to the beginning and end of the string expression and thereby makes a string expression a valid SQL Server identifier. QUOTENAME function is useful when working with database object names that contain spaces and reserved words. Generally it's a bad idea to use reserved words, special characters and spaces inside your object names. However at times, such as when working with 3rd party software, you do not have a choice. The following example uses QUOTENAME function to create a valid identifier:
SELECT QUOTENAME('column name with spaces')
Results:
[column name with spaces]