SQL Server String Functions

String functions

In developing T-SQL, it is often necessary to perform a variety of operations on strings, and the following describes commonly used string functions.

　　1. ASCII ASCII code to get characters

The ASCII code is the standard encoding of the character. To get the ASCII code for a character, you can do so by calling the ASCII function.

Syntax structure:

ASCII (espression)

The expression here is one that returns a char or varchar data type, and the ASCII function returns only the ASCII value for the leftmost character of the expression.

return value: Int data type.

Example:

Select ASCII (' F ')    --Output 102

Note If it is a string of more than one character, it simply returns the ASCII value of the first character.

　　2. Obtain the ASCII code corresponding to the character Char

Syntax structure:

char (integer_expression)

return value type: char type character

Example:

Select char (102)    --Output F

　　3. Get Unicode encoding Unicode for characters

Unicode functions can obtain Unicode encoding of characters.

Syntax structure:

Unicode (' ncharacter_expression ')

The ncharacter_expression here is an expression of type nchar or nvarchar. function only returns the Unicode encoding of the first character.

return value: Int type data

Example:

SELECT Unicode (' Fly ')    --Output 39134

　　4. Obtain the Unicode encoding corresponding to the character nchar

NChar can return Unicode characters with the specified integer code, as defined by the Unicode standard.

Syntax structure:

NCHAR (integer_expression)

return value: Unicode character

Example:

SELECT nchar (39134)        --Output fly

　　5, get the first occurrence of the string position Patindex

The PATINDEX function returns the position of the first occurrence of a pattern in the specified expression and returns 0 if the pattern is not found in all valid text and character data types.

Syntax structure:

Patindex ('%pattenr% ', expression)

Parameter description:

Pattern: a text string. You can use wildcard characters, but you must have a% character before and after the pattern (except when you search for the first or last character). Because in T-SQL,% represents pattern matching.

Return value: If the data type of expression is varchar (max) or nvarchar (max), then bigint, otherwise int, which is the first occurrence of the string specified by pattern in expression.

Example:

Select Patindex ('%llo% ', ' Hello World ')    --Output 3, first occurrence Llo is in 3rd character position

　　6. Generate a space string

The space function can generate a string of any number of spaces.

Syntax structure:

Space (integer_expression)

The integer_expression here is a positive integer indicating the number of spaces. If you want to include spaces or more than 8,000 spaces in your Unicode data. You need to use the replicate function.

return value: Char character of data type.

Example:

Select (' Hello ' + space (5) + ' world ')    --Output Hello World     

　　7. Repeat the generation of the string by a specified number of times replicate

The replicate function can repeat an expression in the form of a string as a specified number of times, resulting in a string.

Syntax structure:

Replicate (Character_expression,integer_expression)

Parameter description:

character_expression: An alphanumeric expression of character data, or an alphanumeric expression that can be implicitly converted to a varchar data type.

Integer_expression: A positive integer. If integer_expression is negative, an error is returned. Integer_expression can make the bigint type.

Return value: A string of the same data type as character_expression.

Example:

Select Replicate (' Hello ', 5)    --Output Hellohellohellohellohello

　　8. Intercept string substring

The SUBSTRING function can intercept strings in a string.

Syntax structure:

SUBSTRING (expression,start,length)

　　　　Parameter description:

Expression: is a string, binary string, text, image, column, or expression that contains a column, but do not use an expression that contains an aggregate function.

Start: An integer specifying the starting position of the substring at which start can make the bigint type.

Length: A positive integer that specifies the number of characters or bytes of expression to return. If length is negative, an error is returned. Length allows the bigint data type.

return value:

If expression is a supported character data type, character data is returned.

If expression is a supported binary data type, binary data is returned.

Example:

Select substring (' Hello ')    --output He is a little different from C #, and its first character starts with 1.

　　9. Get the string length len

The Len function is used to get the length (number of characters) of a string, but does not include the right space. The left space and the space on the right are counted.

Syntax structure:

Len (string_expression)

Parameter description:

String_Expression: A string to calculate the length of.

Return value: The expression data type is varchar (max), nvarchar (max), or varbinary (max), or bigint. otherwise, an int.

Example:

Select Len (' The big, all the world ')    --Output 9

　　10. Replace string content Stuff

The stuff function is used to remove characters of a specified length from a specified string and to insert a different set of characters at the starting point.

Syntax structure:

Stuff (Source_character_expression,start,length,destination_character_expression)

Parameter description:

Source_character_expression: source string. It can be a constant, a variable, or a character column or binary data columns.

Start: An integer that specifies the start position of the 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 a bingint type.

Length: A books that specifies the number of characters to delete.　　　　　　　　　　If length is longer than the first charter_expression, it is removed up to the last character in the last character_expression. Length can be a bigint type.

Destination_character_expression: The destination string. It can be a constant, a variable, or a character column or binary data columns. The insert is executed in the source string.

Example:

Select Stuff (' 123456789 ', 5,2, ' xyz ')    --Output 1234xyz780    starts with the 5th character and replaces the 5th 62 characters with the XYZ string

11. Specify the location of the search string for content charindex

The CHARINDEX function is used to search for a specific string in the specified string, and can specify where to start the search, returning the number of characters to find the target string for the first time.

Syntax structure:

CHARINDEX (expression1, expression2 [, Start_location])

Parameter description:

Expression1: An expression of a string data type that contains the sequence of characters to find.

Expression2: An expression of a string data type, typically a column that is searched for a specified sequence.

Start_location: Start searching in expression2 for expression1 is the character position.　　　　　　If start_location is not specified, is a negative number, or 0, the search begins at the beginning of the expression2. Start_location can be of type bingint.

Return value: bigint if the data type of expression2 is varchar (max), nvarchar (max), or varbinary (max), otherwise int.

Example:

Select CHARINDEX (' 456 ', ' 123456789 ')    --Output 4    retrieves 456 occurrences of the position from 123456789 Select CHARINDEX (' 456 ', ' 123456789 ', 4)    --Output 4

　　12. Generate a delimited Unicode string QUOTENAME

The QuoteName function is used to generate a Unicode string with delimiters.

(1), separator

It can be single quotation marks ('), left and right square brackets ([]), or double quotation marks ("). If not specified, square brackets are used. A Unicode string with square brackets, such as: [Hello].

(2), grammatical structure:

QuoteName (' character_string ' [, ' Quote_character '])

Parameter description:

A string that consists of Character_string:unicode string data.

Quote_character: A single character string used as a delimiter.

(3), return value

nvarchar (258), generate a string with a delimiter length of not more than 258

Example:

Select QuoteName (' I am a soldier ')    --Output [I am a pawn] Select QuoteName (' I am a soldier ', ' "')    --Output" I am a Soldier "

　　13. Convert floating-point numbers to string STR

The STR function is used to convert floating-point numbers to strings

Syntax structure:

STR (Float_expression[,length[,decimal])

Float_espression: An expression of an approximate numeric (float) data type with a decimal point.

Length: total lengths. It includes decimal points, symbols, numbers, and spaces. The default value is 10.

Decimal: The number of digits after the decimal point. Decimal must be less than or equal to 16. If decimal is greater than 16, the result is truncated so that it remains only 16 digits after the decimal point.

return value:

Char: fixed-length string.

Example:

Select STR (123.456789)    -Output 123 Select STR (123.456789,7)-Output    123    Note that the preceding is 4 spaces, because the number of bits left after the decimal point is not written, so the empty sel is automatically populated ECT STR (123.456789,7,3)    --Output 123.457 the    last one rounded up, with a total length of 7 decimal points reserved 3 digits

　　14. Truncate left string

The left function is used to intercept a string of the specified length, starting with the first character on the other. Its execution effect equals substring (expression,1,length).

Syntax structure:

Left (character_expression,integer_expression)

Parameter description:

Character_expression: A character or binary expression, which can be a constant, a variable, or an expression.

Integer_expression: A positive integer that specifies the number of characters returned.

return value:

varchar or nvarchar, variable-length string.

Example:

Select Left (' 123456789 ', 3)    --Output 123

　　　15. Truncate RIGHT string

The right function is used to intercept a string of the specified length starting with the first character on the left. Its execution effect equals sunstring (Expression,len (expression)-length+1,length).

Syntax structure:

Right (Character_expression,integer_expression)

Parameter description:

Character_expression: A character or binary expression, which can be a constant, a variable, or an expression.

Integer_expression: A positive integer that specifies the number of characters returned.

return value:

varchar or nvarchar, variable-length string.

Example:

Select Right (' 123456789 ', 3)    --Output 789

　　　16. Clear the left space LTrim

If the character of a string is a space, the LTrim function is used to clear contiguous left spaces.

Syntax structure:

LTrim (character_expression)

Character_expression is either a character or a binary data expression, which can be a constant, variable, or data column.

return value:

varchar or nvarchar, variable-length string.

Example:

Select LTrim ('    123456789 ')  --output 123456789, note that the left-hand space has been deleted, the output string to the left is no space

　　17. Clear the right space RTrim

The RTrim function is used to empty contiguous spaces on the right.

Syntax structure:

RTrim (character_expression)

Character_expression is either a character or a binary data expression, which can be a constant, variable, or data column.

return value:

varchar or nvarchar, variable-length string.

Example:

Select RTrim (' 123456789   ')    --output 123456789, note that the right space has been cleared

　　18. Convert to lowercase string lower

The lower function returns after all the strings have been converted to lowercase characters.

Syntax structure:

Lower (character_expression)

return value:

varchar or nvarchar, variable length string

Example:

Select lower (' ABCDEFG ')    --The output ABCDEFG has all been converted to lowercase

　　19. Convert to uppercase String upper

The upper function is used to return all strings to uppercase characters.

Syntax structure:

Upper (character_expression)

Character_expression is either a character or a binary expression, which can be a constant, variable, or data column.

return value:

varchar or nvarchar, variable-length string.

Example:

Select Upper (' ABCDEFG ')    --output ABCDEFG Notice that it's all converted to uppercase.

　　20. Reverse Order String Reverse

The reverse function is used to reverse the contents of a string and return it.

Syntax structure:

Reverse (character_expression)

character_expression: A binary or binary data expression, which can be a constant, variable, or data column.

return value:

varchar or nvarchar, variable length string

Example:

Select reverse (' 123456789 ')    --Output 987654321 Note that the order has been reversed.

　　21. Get the string byte number Datalength

The DATALENGTH function is used to get the number of bytes of a string, not the number of characters. This function is not only suitable for string data, but also suitable for any type of data, such as text (text, ntext), binary data (varbinary, binary), and images (image).

Syntax structure:

DATALENGTH (expression)

return value:

If the expression data type is varchar (max), nvarchar (max), or varbinary (max) data type, the bigint is returned, otherwise int is returned.

Example:

Select Datalength (' I am a pawn ')    --Output of the ten Select Len (' I am a soldier ')    --Output 5 Select datalength (' ABCDE ')    --Output 5 Select Len (' ABCDE ')    --Output 5

　　22, SOUNDEX

Returns a four-character code (SOUNDEX) that evaluates the similarity of two strings.

Select SOUNDEX (' ABCDE ')    --a120 Select SOUNDEX (' Abcdr ')--a121

　　23. REPLACE

Replaces all occurrences of the specified string value with another string value.

The syntax is as follows:

REPLACE (String_expression1, String_expression2, String_expression3)

Parameters
String_expression1: The string expression to search for. String_expression1 can be either a character or a binary data type.
String_expression2: the substring to find. String_expression2 can be either a character or a binary data type.
String_expression3: Replace String. String_expression3 can be either a character or a binary data type.

SELECT REPLACE (' ABCDE ', ' abc ', ' xxx ')    --xxxde

　　24, Difference

Returns an integer value that indicates the difference between the SOUNDEX values of a two-character expression.

SELECT difference (' ABCDE ', ' abc ')    --4

SQL Server String Functions