SQL string processing functions

String functions perform different operations on binary data, strings, and expressions. This type of function applies to CHAR, VARCHAR, BINARY, and VARBINARY data types, and data types that can be implicitly converted to CHAR or VARCHAR.

I. character conversion functions

1. ASCII () and CHAR ()

ASCII () returns a character expression. Leftmost character. In the ASCII () function, strings with numbers are not enclosed by '', But strings with other characters must be enclosed by''. Otherwise, an error occurs. CHAR () converts an ASCII code to a character. If no value is entered ~ ASCII code value between 128, CHAR () returns NULL. For example:

Select ASCII ('asd ') select CHAR (97) select CHAR (129) -- char () ranges from 0 to 128

Output result:
2. Unicode () and NCHAR () are similar to ASCII () and CHAR () functions. They appear in pairs. In other words, Unicode () is used () when converting a character to an ASCII code value, the corresponding NCHAR () should be used to convert the ASCII code value to a character. Likewise, ASCII () is used () when converting a character to an ASCII value, the corresponding ASCII value should be converted to a character using CHAR. NCHAR () converts an ASCII code to a character. If no value is entered ~ ASCII code value between 65535, CHAR () returns NULL. For example:

Select Unicode ('asd ') select NCHAR (97) select NCHAR (65536) -- char () range: 0-65535

Output result:
3. LOWER () and UPPER () LOWER () convert all strings to lowercase letters; UPPER () convert all strings to uppercase letters. 4. STR () converts numeric data to numeric data. STR (<float_expression> [, length [, <decimal>]) length specifies the length of the returned string, and decimal indicates the number of decimal places returned. If no length is specified, the default length value is 10 and the default value of decimal is 0. If the value of length or decimal is negative, NULL is returned. If the length is smaller than the number of digits to the left of the decimal point, the length * is returned. The length is subject to the length before decimal. When the number of digits of the returned string is smaller than the length, fill in spaces on the left. For example:

Select STR (1.11111, 2) -- return value 1. When the number of returned strings is less than length, the left side fill in the space select STR (11.1111, 2) -- return value *, when length is less than the number of digits to the left of the decimal point, return length * select STR (1.11111,-1.11111) -- return NULL. When length or decimal is negative, return NULL select STR, 3, 2) -- Return Value 1.1, obey length first, then take decimal

2. Remove space functions 1. LTRIM () Remove spaces in the string header. 2. RTRIM () removes spaces at the end of the string. 3. Functions 1 and left () LEFT (<character_expression>, <integer_expression>) return character_expression, which starts from integer_expression on the left. 2. RIGHT () RIGHT (<character_expression>, <integer_expression>) returns character_expression, which is a string of up to integer_expression characters. 3. SUBSTRING () SUBSTRING (<expression>, <starting _ position>, length) returns the part of the length from the starting _ position Character on the left of the string. For example:

Select LEFT ('abcd', 6) -- Return Value: abcdselect LEFT ('abcd', 2) -- Return Value: abselect RIGHT ('abcd', 6) -- Return Value: abcdselect RIGHT ('abcd', 2) -- Return Value: cdselect SUBSTRING ('abcd',) -- Return Value: abselect SUBSTRING ('abcd) -- return value is aselect SUBSTRING ('abcd',) -- return value is null select SUBSTRING ('abcd',-) -- return value is null select SUBSTRING ('abcd) -- return value is

Iv. String comparison functions 1. CHARINDEX () return the starting position of a specified substring in the string. CHARINDEX (<'substring _ expression'>, <expression>) Where substring _ expression is the character expression to be searched, and expression can be a string or a column name expression. If no substring is found, the return value is 0. This function cannot be used for TEXT and IMAGE data types. 2. PATINDEX () returns the starting position of a specified substring in the string. PATINDEX (<'% substring _ expression %'>, <column _ name>) must have a percent sign (%) before and after the substring expression; otherwise, the return value is 0. Unlike the CHARINDEX function, wildcards can be used in the child strings of the PATINDEX function, which can be used for CHAR, VARCHAR, and TEXT data types. For example:

Select CHARINDEX ('AB', 'abcd') -- returns 1 select PATINDEX ('% AB %', 'abcd') -- returns 1

5. String operation functions 1. QUOTENAME () return the string that is enclosed by a specific character. QUOTENAME (<'character _ expression'> [, quote _ character]). quote _ character indicates the character used to enclose the string. The default value is "[]". 2. REPLICATE () returns a string that repeats character_expression for a specified number of times. REPLICATE (character_expression integer_expression) if the value of integer_expression is negative, NULL is returned. 3. REVERSE () reverses the character arrangement order of the specified string. REVERSE (<character_expression>) Where character_expression can be a string, a constant, or a column value. 4. REPLACE () returns the string with the specified substring replaced. REPLACE (<string_expression1>, <string_expression2>, <string_expression3>) Use string_expression3 to REPLACE string_expression2 in string_expression1. 4. SPACE () returns a blank string with a specified length. SPACE (<integer_expression>) if the value of integer_expression is negative, NULL is returned. 5. Use another substring to replace the specified position and length of the substring. STUFF (<character_expression1>, <start _ position>, <length>, <character_expression2>) if the start position is negative or the length value is negative, or the start position is greater than the length of character_expression1, the return value is NULL. For example:

Select QUOTENAME ('abcd', '{}') -- Return Value: {abcd} select QUOTENAME ('abcd') -- Return Value: [abcd] select REPLICATE ('abcd', 2) -- return value is abcdabcdselect REPLICATE ('abcd', 0) -- return value is null. If it is negative, return NULLselect REVERSE ('abcd') -- return value is dcbaselect REPLACE ('abcd ', 'AB', 'E') -- Return Value: ecdselect REPLACE (SPACE (2), '', 'A') -- Return Value: aaselect SPACE (2) -- returns two spaces: select STUFF ('abcd', 'ef ') -- returns abefselect STUFF ('abcd', 'ef') -- returns NULL, if the starting position (5) is greater than the length of character_expression1 (abcd), the return value is NULL select STUFF ('abcd', 'ef '). The return value is ef.