Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Restart / Reset / Reseed an Identity Column From 1
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 )

Related Articles :