STRING Functions

ASCII(character_expression)

Returns the ASCII code value of the leftmost character of the character expression.

character_expression: The character expression.

CHAR(integer_expression)

Converts the integer ASCII code to the corresponding character.

integer_expression: The integer from 0 through 255.

CHARINDEX(expressionToFind ,expressionToSearch [,start_location ])

Returns the starting position of the specified expression in the character string.

expressionToFind: The character expression to find.
expressionToSearch: The character expression, typically a column, to search.
start_location: The optional character position to start searching for expressionToFind in expressionToSearch.

CONCAT(string_value1, string_value2 [, string_valueN])

Returns the string that is the concatenation of two or more string values.

string_value1: The first string to be concatenated.
string_value2: The second string to be concatenated.
*: The arbitrary number of strings to be concatenated.

FORMAT(value, format)

Returns the value formatted with the specified format.

value: The string to format.
format: The string specifying the syntax of the date format.

ISNULL ( check_expression , replacement_value )

Replaces null with the specified replacement value.

check_expression: The expression to be checked for null.
replacement_value: The expression to be returned if check_expression is null.

JSON_EXTRACT(json, jsonpath)

Selects a value in json according to the JSONPath expression json_path. The json_path parameter must be a string constant. Returns the value in JSON string format.

json: The JSON document to extract.
jsonpath: The json path used to select the nodes. The value of the nodes selected will be returned in a token-separated list.

LEFT ( character_expression , integer_expression )

Returns the specified number of characters counting from the left of the specified string.

character_expression: The character expression.
integer_expression: The positive integer that specifies how many characters will be returned counting from the left of character_expression.

LEN(string_expression)

Returns the number of characters of the specified string expression.

string_expression: The string expression.

LOWER ( character_expression )

Returns the character expression with the uppercase character data converted to lowercase.

character_expression: The character expression.

NCHAR(integer_expression)

Returns the Unicode character with the specified integer code as defined by the Unicode standard.

integer_expression: The integer from 0 through 255.

PATINDEX(pattern, expression)

Returns the starting position of the first occurrence of the pattern in the expression. Returns 0 if the pattern is not found.

pattern: The character expression that contains the sequence to be found. The wild card character % can be used only at the start or end of the expression.
expression: The expression, typically a column, to search for the pattern.

QUOTENAME(character_string [, quote_character])

Returns a valid SQL Server-delimited identifier by adding the necessary delimiters to the specified Unicode string.

character_string: The string of Unicode character data. The string is limited to 128 characters. Inputs greater than 128 characters return null.
quote_character: The optional single character to be used as the delimiter. Can be a single quotation mark, a left or right bracket, or a double quotation mark. If quote_character is not specified brackets are used.

REPLACE(string_expression, string_pattern, string_replacement)

Replaces all occurrences of a string with another string.

string_expression: The string expression to be searched. Can be a character or binary data type.
string_pattern: The substring to be found. Cannot be an empty string.
string_replacement: The replacement string.

REPLICATE ( string_expression ,integer_expression )

Repeats the string value the specified number of times.

string_expression: The string to replicate.
integer_expression: The repeat count.

REVERSE ( string_expression )

Returns the reverse order of the string expression.

string_expression: The string.

RIGHT ( character_expression , integer_expression )

Returns the right part of the string with the specified number of characters.

character_expression: The character expression.
integer_expression: The positive integer that specifies how many characters of the character expression will be returned.

RTRIM(character_expression)

Returns the character expression after it removes trailing blanks.

character_expression: The character expression.

SOUNDEX(character_expression)

Returns the four-character Soundex code, based on how the string sounds when spoken.

character_expression: The alphanumeric expression of character data.

SPACE(repeatcount)

Returns the string that consists of repeated spaces.

repeatcount: The number of spaces.

STR ( float_expression [ , length [ , decimal ] ] )

Returns the character data converted from the numeric data. For example, STR(123.45, 6, 1) returns 123.5.

float_expression: The float expression.
length: The optional total length to return. This includes decimal point, sign, digits, and spaces. The default is 10.
decimal: The optional number of places to the right of the decimal point. The decimal must be less than or equal to 16.

STUFF(character_expression , start , length , replaceWith_expression)

The STUFF function inserts a string into another string. It deletes the specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

character_expression: The string expression.
start: The integer value that specifies the location to start deletion and insertion. If start or length is negative, null is returned. If start is longer than the string to be modified, character_expression, null is returned.
length: The integer that specifies the number of characters to delete. If length is longer than character_expression, deletion occurs up to the last character in replaceWith_expression.
replaceWith_expression: The expression of character data that will replace length characters of character_expression beginning at the start value.

SUBSTRING(expression,start,length)

Returns the part of the string with the specified length; starts at the specified index.

expression: The character string.
start: The positive integer that specifies the start index of characters to return.
length: The positive integer that specifies how many characters will be returned.

UNICODE(ncharacter_expression)

Returns the integer value defined by the Unicode standard of the first character of the input expression.

ncharacter_expression: The Unicode character expression.

UPPER ( character_expression )

Returns the character expression with lowercase character data converted to uppercase.

character_expression: The character expression.

XML_EXTRACT(xml, xpath [, separator])

Extracts an XML document using the specified XPath to flatten the XML. A comma is used to separate the outputs by default, but this can be changed by specifying the third parameter.

xml: The XML document to extract.
xpath: The XPath used to select the nodes. The value of the nodes selected will be returned in a token-separated list.
separator: The token used to separate the items in the flattened response. If this is not specified, the separator will be a comma.