SQL Server String functions (super-practical)

• 1. Len (): Calculates the string length
• 2. Lower (), Upper (): string converted to large, lowercase
• 3. LTrim (), RTrim (): Truncate the left and right spaces of a string
• 4. Space (): Returns a string consisting of repeated spaces
• 5. Substring (), left (), right (): Take substring
• 6. Replace (): string substitution
• 7. Reverse (): Returns the inverse value of a string value
• 8. Stuff (): Deletes a specified length of character and inserts another set of characters at the specified starting point
• 9. Replicate (): Repeats a string value for a specified number of times
• CHARINDEX (): Returns the starting position of the specified expression in the string
• PATINDEX (): Returns the start position of the first occurrence of the pattern in the specified expression
• Unicode (): Returns the integer value of the first character of the input expression
• STR (): Returns character data converted from numeric data
• ASCII (): Gets the ASCII code of the character
• A. Char (): Gets a character corresponding to the ASCII code number
• NCHAR (): Returns the Unicode character with the specified integer code
• QuoteName () escape
• Soundex (): Pronunciation matching degree

For example, all of the following are examples of studnet tables:

1. Len (): Calculates the string length

Len () is used to calculate the length of a string, each Chinese character or English letter is a length

Select Sname, Len (sname) from student

2. Lower (), Upper (): string converted to large, lowercase

Lower () is used to convert a string to lowercase, and upper () is used to convert a string to uppercase

Select Lower (' I AM A STUDENT! ') Select Upper (' I am a student! ')

3. LTrim (), RTrim (): Truncate the left and right spaces of a string

LTrim (): Used to remove a space to the left of a string, RTrim () is used to remove a space to the right of a string

declare @str varchar; set @str = ' There are spaces on my left! '; Select @str as initial character, Len (@str) as initial length, LTrim (@str) as existing character, Len (LTrim (@str)) as existing length;

Return results: initial lengths of 9 and 8, respectively

4. Space (): Returns a string consisting of repeated spaces

Space (integer_expression): integer_expression A positive integer indicating the number of spaces. If integer_expression is negative, an empty string is returned.

Select ' A ' + space (2) + ' B '/    * Result: ' A  B '/*

5. Substring (), left (), right (): Take substring

• SUBSTRING (string, start_position, length): A substring of any length can be taken from any position,

• Left (string, length): The substring is taken from the start

• Right (string, length): The substring is taken from the start

Select substring (' helloworld! ', 6, 6)/   * results: World */select Left (' helloworld! ', 5)/           * Result: Hello */select Right (' Helloworld! ', 6)          /* Results: Oworld */

6. Replace (): string substitution

Replace (string, strings to be replaced, strings replaced)

Select replace (' helloworld! ', ' o ', ' e ')/   * The result is: hellewerld! */

7. Reverse (): Returns the inverse value of a string value

Reverse (string_expression)

Select reverse (' abc ')/   * results are: cba*/

8. Stuff (): Deletes a specified length of character and inserts another set of characters at the specified starting point

Stuff (character_expression, start, length, character_expression) start: integer specifying the start position of the delete and insert. Length: An integer that specifies the number of characters to delete.

Select Stuff (' ABCDEFGF ', 2, 6, ' hello-')/    * results are: ahello-f */    Select stuff (' ABCDEFGF ', 2, 6, ' Hello ')/        * Result: AH Ellof * *

9. Replicate (): Repeats a string value for a specified number of times

Replicate (String_Expression, integer_expression)

Select Replicate (' ABC ', 4)/  * The result is: ABCABCABCABC */

CHARINDEX (): Returns the starting position of the specified expression in the string

CHARINDEX (expression1, expression2, start_location) or charindex (expression1, expression2)

expression1 start position in expression2

Select CHARINDEX (' H ', ' ellohworldhabc ')/    * The result is: 5 */

PATINDEX (): Returns the start position of the first occurrence of the pattern in the specified expression

Patindex ('%pattern% ', expression): Returns the starting position of the first occurrence of a pattern in the specified expression, or zero if the pattern is not found in all valid text and character data types.

Select Patindex ('%hello% ', ' Worldhello ') results are: 6

Unicode (): Returns the integer value of the first character of the input expression

Unicode (' ncharacter_expression '): ' ncharacter_expression ' is an expression of nchar or nvarchar.

Select Unicode (' a ')/        * result is: */select Unicode (' abc ')//    * Result: 97 */

STR (): Returns character data converted from numeric data

STR (float_expression, length, decimal) float_expression: 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 decimals to the right of the decimal point. Decimal must be less than or equal to 16. If decimal is greater than 16, the result is truncated to the 16-bit to the right of the decimal point.

Select STR (1234.436, 3)/         * results are: ' * * '; returns ' * * ' */select len (str (1234.436, 3)/* If the expression exceeds the specified length)/*     results are: 3 */    Select str (123.436), Len (str (123.436))/              * results are: ' 123 ', */select str (123.436, 6), Len (str (123.436, 6))/        * The result is: ' 123 ', 6 */se Lect Str (123.436, 6, 1), Len (str (123.436,6, 1))/   * The result is: ' 123.4 ', 6 */

ASCII (): Gets the ASCII code of the character

ASCII (): Used to get the ASCII code of a character, it has only one parameter, if the argument is a string, then take the first character of the ASCII code

Select ASCII (' H ')/               * Result: */select ASCII (' helloworld! ')     /* Results are: 72 */

A. Char (): Gets a character corresponding to the ASCII code number

Char (integer_expression): integer_expression An integer between 0 and 255. If the integer expression is not within this range, a NULL value is returned.

Select Char/     * results are: H */

NCHAR (): Returns the Unicode character with the specified integer code

NCHAR (integer_expression) integer_expression a positive integer between 0 and 65535. If a value beyond this range is specified, NULL is returned.

Select NCHAR (20013)//The        result is: ' Medium ' */select nchar (        * * * Result: ' H '/

QuoteName () escape

Refer to "QuoteName usages and examples" in SQL Server

Soundex (): Pronunciation matching degree

Sometimes we don't know the spelling of a person's name, but we know his pronunciation, and then we can make a matching test of the pronunciation. Soundex (): Used to calculate the pronounced characteristics of a string, which is a four-character string, the first character of the feature is always the first character in the initial string, and then a three-digit numeric value.

Select Sname, Soundex (sname) from student

The result is:

The meaning of the pronounced eigenvalues is very complex, and it is cumbersome to analyze the pronunciation similarity of two strings based on the two pronounced eigenvalues.

You can use difference () to simplify the pronunciation similarity comparison of two strings, which can calculate the pronounced eigenvalues of two strings, compare them, and then return a value between 0~4 to reflect the pronunciation similarity of two strings, the larger the value, the greater the pronunciation similarity of the two strings.

Select Sname, Soundex (sname), Difference (sname, ' Herry ') from Stu

The result is:

SQL Server String functions (super-practical)