String Functions

Article
04/12/2011

The .NET Framework Data Provider for SQL Server (SqlClient) provides String functions that perform operations on an input String and return a String or numeric value result. These functions are in the SqlServer namespace, which is available when you use SqlClient. A provider's namespace property allows the Entity Framework to discover which prefix is used by this provider for specific constructs, such as types and functions.

The following table shows the SqlClient String functions.

Function	Description
ASCII(expression)	Returns the ASCII code value of the leftmost character of a string expression. Arguments expression: Any valid expression of an ASCII String type. Return Value An Int32. Example `SqlServer.ASCII('A')`
CHAR(expression)	Converts an Int32 code to an ASCII String. Arguments expression: An Int32. Return Value An ASCII String. Example `SqlServer.char(97)`
CHARINDEX(expression1, expression2 [, start_location])	Returns the starting position of the specified expression in a character string. Arguments expression1: An expression that contains the sequence of characters to be found. The expression can be of a String (ASCII or Unicode) type or of a Binary type. expression2: An expression, typically a column, to be searched for the specified sequence. The expression can be of a String (ASCII or Unicode) type or of a Binary type. start_location: (Optional) An Int64 (not returned in SQL Server 2000) or Int32 that represents the character position to start searching for expression1 in expression2. If start_location is not specified, is a negative number, or is zero, the search starts at the beginning of expression2. Return Value An Int32. Example `SqlServer.CHARINDEX('h', 'habcdefgh', 2)`
DIFFERENCE(expression, expression)	Compares the SOUNDEX values of two strings and evaluates the similarity between them. Arguments An ASCII or Unicode String type. expression can be a constant, a variable, or a column. Return Value Returns an Int32 that represents the difference between the SOUNDEX values of two character expressions. The range is from 0 through 4. 0 indicates a weak similarity or no similarity, and 4 indicates a strong similarity or the same values. Example `// The following example returns a DIFFERENCE value of 4,` `//the least possible difference or the best match.` `SqlServer.DIFFERENCE('Green','Greene');`
LEFT(expression, count)	Returns the left part of a character string with the specified number of characters. Arguments expression: A Unicode or ASCII String type. Use the CAST function to explicitly convert character_expression. count: An Int64 (not returned in SQL Server 2000) or Int32 type that specifies how many characters of character_expression will be returned. Return Value A Unicode or ASCII String. Example `SqlServer.LEFT('SQL Server', 4)`
LEN(expression)	Returns the number of characters in the specified String expression, excluding trailing blanks. Arguments expression: An expression of a String (Unicode or ASCII) type or a Binary type Return Value An Int32. Example `SqlServer.LEN('abcd')`
LOWER(expression)	Returns a String expression after converting uppercase character data to lowercase. Arguments expression: Any valid expression of the String type. Return Value A String. Example `SqlServer.LOWER('AbB')`
LTRIM(expression)	Returns a String expression after removing leading spaces. Arguments expression: Any valid expression of String type. Return Value A String. Example `SqlServer.LTRIM(' d')`
NCHAR(expression)	Returns a Unicode String with the specified integer code, as defined by the Unicode standard. Arguments expression: An Int32. Return Value A Unicode String. Example `SqlServer.NCHAR(65)`
PATINDEX('%pattern%', expression)	Returns the starting position of the first occurrence of a pattern in a specified String expression. Arguments '%pattern%': An ASCII or Unicode String type. Wildcard characters can be used; however, the % character must come before and after pattern (except in searches for first or last characters). expression: An ASCII or Unicode String to search for the specified pattern. Return Value An Int32. Example `SqlServer.PATINDEX('abc', 'ab')`
QUOTENAME('char_string' [, 'quote_char'])	Returns a Unicode String with the delimiters added to make the input string a valid SQL Server 2005 delimited identifier. Arguments char_string: A Unicode String. quote_char: A one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [ ] ), or a double quotation mark ( " ). If quote_char is not specified, brackets are used. Return Value A Unicode String. Example `SqlServer.QUOTENAME('abc[]def')`
REPLACE(expression1, expression2, expression3)	Repeats a character expression for a specified number of times. Arguments expression1: The string expression to be searched. string_expression1 can be of a Unicode or an ASCII String type. expression2: The substring to be found. string_expression2 can be of a Unicode or an ASCII String type. expression3; The replacement string. string_expression3 can be of a Unicode or an ASCII String type. Example `SqlServer.REPLACE('aabbcc', 'bc', 'zz')`
REPLICATE(char_expression, int_expression)	Repeats a character expression for a specified number of times. Arguments char_expression: A Unicode or ASCII String type. int_expression: Int64 (not supported in SQL Server 2000) or Int32. Return Value A Unicode or ASCII String type. Example `SqlServer.REPLICATE('aa',2)`
REVERSE(expression)	Returns a Unicode or ASCII String with its character positions reversed from the input string. Arguments expression: A Unicode or ASCII String type. Return Value A Unicode or ASCII String type. Example `SqlServer.REVERSE('abcd')`
RIGHT(char_expression, count)	Returns the right part of a character string with the specified number of characters. Arguments char_expression: A Unicode or ASCII String type. Use the CAST function to explicitly convert character_expression. count: An Int64 (not returned in SQL Server 2000) or Int32 type that specifies how many characters of character_expression will be returned. Return Value An ASCII String type. Example `SqlServer.RIGHT('SQL Server', 6)`
RTRIM(expression)	Returns a Unicode or ASCII String after removing trailing spaces. Arguments expression: A Unicode or ASCII String type. Return Value A Unicode or ASCII String type. Example `SqlServer.RTRIM(' d e ')`
SOUNDEX(expression)	Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.Arguments expression: A Unicode or ASCII String type. Return Value An ASCII String. A four-character (SOUNDEX) code is a string that evaluates the similarity of two strings. Example `Select SqlServer.SOUNDEX('Smith'), SqlServer.SOUNDEX('Smythe') FROM {1}` Returns `----- ----- S530 S530`
SPACE(int_expression)	Returns an ASCII String of repeated spaces. Arguments int_expression: An Int64 (not returned in SQL Server 2000) or Int32 that indicates the number of spaces. Return Value An ASCII String. Example `SqlServer.SPACE(2)`
STR(float_expression [, length [, decimal]])	Returns an ASCII String converted from numeric data. Arguments float _expression: A expression of approximate numeric (Double) data type with a decimal point. length: (optional) An Int32 that represents the total length. This includes decimal point, sign, digits, and spaces. The default is 10. decimal: (optional) An Int32 that represents the number of places to the right of the decimal point. decimal must be less than or equal to 16. If decimal is more than 16, the result is truncated to sixteen places to the right of the decimal point. Return Value An ASCII String. Example `SqlServer.STR(212.0)`
STUFF(str_expression, start, length, str_expression_to_insert)	Deletes a specified length of characters and inserts another set of characters at a specified starting point in a string expression. Arguments str_expression: A Unicode or ASCII String. start: An Int64 (not returned in SQL Server 2000) or Int32 value that specifies the location to start the deletion and insertion. length: An Int64 (not returned in SQL Server 2000) or Int32 value that specifies the number of characters to delete. str_expression_to_insert: A Unicode or ASCII String. Return Value A Unicode or ASCII String. Example `SqlServer.STUFF('abcd', 2, 2, 'zz')`
SUBSTRING(str_expression, start, length)	Returns part of a String expression. Arguments str_expression: An expression of a String (ASCII or Unicode) type or a Binary type. start: An Int64 (not returned in SQL Server 2000) or Int32 that specifies where the substring starts. 1 refers to the first character in the string. length: An Int64 (not returned in SQL Server 2000) or Int32 that specifies how many characters of the expression will be returned. Return Value A String (ASCII or Unicode) type or a Binary type. Example `SqlServer.SUBSTRING('abcd', 2, 2)`
UNICODE(expression)	Returns the integer value, as defined by the Unicode standard, for the first character of the input expression. Arguments expression: A Unicode String. Return Value An Int32. Example `SqlServer.UNICODE('a')`
UPPER(expression)	Returns a String expression after converting lowercase character data to uppercase. Arguments expression: An expression of an ASCII or a Unicode String type. Return Value An ASCII or a Unicode String type. Example `SqlServer.UPPER('AbB')`

For more information about the String functions that SqlClient supports, see the documentation for the SQL Server version that you specified in the SqlClient provider manifest:

SQL Server 2000	SQL Server 2005	SQL Server 2008
String Functions (Transact-SQL)	String Functions (Transact-SQL)	String Functions (Transact-SQL)

Share via

String Functions

See Also

Concepts

Additional resources