SQL Server Helper - SQL Server String Functions

Tip of the Day : How to Join with an Inline User-Defined Function

Built-in Functions

Home > Functions > SQL Server String Functions

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. All built-in string functions, except for CHARINDEX and PATINDEX, are deterministic. This means they return the same value any time they are called with a specific set of input values.

Function	Description
ASCII	Returns the ASCII code value of the leftmost character of a character expression. Syntax: ASCII ( character_expression )
CHAR	Converts an int ASCII code to a character. Syntax: CHAR ( integer_expression )
CHARINDEX	Returns the starting position of the specified expression in a character string. Syntax: CHARINDEX ( expression1 ,expression2 [ , start_location ] )
DIFFERENCE	Returns an integer value that indicates the difference between the SOUNDEX values of two character expressions. Syntax: DIFFERENCE ( character_expression , character_expression )
LEFT	Returns the left part of a character string with the specified number of characters. Syntax: LEFT ( character_expression , integer_expression )
LEN	Returns the number of characters, instead of the number of bytes, of the specified string expression, excluding trailing blanks. Syntax: LEN ( string_expression )
LOWER	Returns a character expression after converting uppercase character data to lowercase. Syntax: LOWER ( string_expression )
LTRIM	Returns a character expression after it removes leading blanks. Syntax: LTRIM ( string_expression )
NCHAR	Returns the Unicode character with the specified integer code, as defined by the Unicode standard. Syntax: NCHAR ( integer_expression )
PATINDEX	Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. Syntax: PATINDEX ( '%pattern%' , expression )
QUOTENAME	Returns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL Server 2005 delimited identifier. Syntax: QUOTENAME ( 'character_string' [ , 'quote_character' ] )
REPLACE	Replaces all occurrences of the second specified string expression in the first string expression with a third expression. Syntax: REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )

Function	Description
REPLICATE	Repeats a character expression for a specified number of times. Syntax: REPLICATE ( character_expression ,integer_expression )
REVERSE	Returns the reverse of a character expression. Syntax: REVERSE ( character_expression )
RIGHT	Returns the right part of a character string with the specified number of characters. Syntax: RIGHT ( character_expression , integer_expression )
RTRIM	Returns a character string after truncating all trailing blanks. Syntax: RTRIM ( character_expression )
SOUNDEX	Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings. Syntax: SOUNDEX ( character_expression )
SPACE	Returns a string of repeated spaces. Syntax: SPACE ( integer_expression )
STR	Returns character data converted from numeric data. Syntax: STR ( float_expression [ , length [ , ] ] )
STUFF	Deletes a specified length of characters and inserts another set of characters at a specified starting point. Syntax: STUFF ( character_expression , start , length ,character_expression )
SUBSTRING	Returns part of a character, binary, text, or image expression. Syntax: SUBSTRING ( expression ,start , length )
UNICODE	Returns the integer value, as defined by the Unicode standard, for the first character of the input expression. Syntax: UNICODE ( 'ncharacter_expression' )
UPPER	Returns a character expression with lowercase character data converted to uppercase. Syntax: UPPER ( character_expression )