SQL Server String processing function Cheat Sheet

ASCII: Returns the ASCII code value of the leftmost character in a character expression.

Select ASCII (expression)

CHAR: Convert int ASCII code to characters.

CHAR can be used to insert control characters into strings.

Char (9)

Linefeed char (10)

Carriage return character char (13)

CHARINDEX: Search for expression1 in expression2 and return its starting position (if any ). The start position of the search is start_location. If the data type of expression2 is varchar (max), nvarchar (max), or varbinary (max), it is bigint; otherwise, it is int.

CHARINDEX (expression1, expression2 [, start_location])

DIFFERENCE: Returns an integer indicating the difference between the SOUNDEX values of two character expressions.

DIFFERENCE (character_expression, character_expression)

LEFT: Returns the number of characters starting from the left of the string.

LEFT (character_expression, integer_expression)

LEN: Returns the number of characters in a specified string expression, excluding trailing spaces.

LOWER: Converts uppercase data to lowercase data and returns a character expression.

LTRIM: Returns the character expression that deletes leading spaces.

NCHAR: Return Unicode characters with the specified integer code according to the definition of the Unicode standard.

NCHAR (integer_expression)

PATINDEX: Returns the starting position of a mode that appears for the first time in a specified expression. If this mode is not found in all valid text and character data types, zero is returned.

PATINDEX ('% pattern %', expression)

QUOTENAME: Returns a Unicode string with delimiters. The addition of delimiters can make the input string a valid Microsoft SQL Server separator.

QUOTENAME ('character _ string' [, 'quote _ character '])

'Character_string'

A string consisting of Unicode characters. Character_string is the sysname value.

'Quote_character'

A single character string used as a separator. It can be single quotation marks ('), left square brackets, right square brackets ([]), or double quotation marks ("). Square brackets are used if quote_character is not specified. '

REPLACE: Replace all specified string values with another string value.

REPLACE (string_expression1, string_expression2, string_expression3)

String_expression1

The string expression to be searched. String_expression1 can be a character or binary data type.


String_expression2

The substring to be searched. String_expression2 can be a character or binary data type.


String_expression3

Replace the string. String_expression3 can be a character or binary data type.

REPLICATE: Repeat the string value by a specified number of times.

REPLICATE (string_expression, integer_expression)

REVERSE: Returns the reverse expression of the character expression.

REVERSE (expression );

RIGHT: Returns the number of characters specified from the right of the string.

RIGHT (character_expression, integer_expression)

RTRIM: Truncates all trailing spaces and returns a string.

SOUNDEX: Return a code (SOUNDEX) consisting of four characters to evaluate the similarity between two strings.

SOUNDEX (character_expression)

SPACE: Returns a string consisting of repeated spaces.

SPACE (integer_expression)

STR: Returns character data converted from numeric data.

STR (float_expression [, length [, decimal])

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

STUFF (character_expression, start, length, character_expression)

Character_expression

A character data expression. Character_expression can be a constant, variable, character column, or binary data column.


Start

An integer that specifies the start position of deletion and insertion. If start or length is negative, an empty string is returned. If start is longer than the first character_expression, an empty string is returned. Start can be of the bigint type.


Length

An integer that specifies the number of characters to delete. If the length is longer than the first character_expression, the last character in the last character_expression can be deleted at most. Length can be of the bigint type.

SUBSTRING: Returns a part of a character expression, Binary Expression, text expression, or image expression.

SUBSTRING (value_expression, start_expression, length_expression)

UNICODE: Returns the integer of the first character of the input expression according to the definition of the Unicode standard.

UNICODE ('ncharacter _ expression ')

UPPER: Returns an expression that converts lowercase data to uppercase.

UPPER (character_expression)