String Functions

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)

See Also

Concepts

SqlClient for Entity Framework Functions
Known Issues in SqlClient for Entity Framework