Shulou(Shulou.com)05/31 Report--

What are the commonly used string handling functions in sql? I believe many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

The details are as follows:

1. Common functions

1. ASCII () returns the ASCII code value of the leftmost character of the character expression. In the ASCII () function, pure numeric strings may not be enclosed with'', but strings containing other characters must be enclosed with'', otherwise an error will occur.

2. CHAR () converts the ASCII code into characters. If you do not enter an ASCII code value between 0 and 255, CHAR () returns NULL.

3. LOWER () and UPPER () LOWER () convert all strings to lowercase; UPPER () converts all strings to uppercase.

4. STR () converts numeric data into character data. STR ([length []]) length specifies the length of the returned string, and decimal specifies the number of decimal places returned. If no length is specified, the default value of length is 10 and the default value of decimal is 0. When length or decimal is negative, return NULL; if length is less than the number of digits to the left of the decimal point (including symbol digits), return length *; obey length first, then take decimal; when the number of string digits returned is less than length, fill in the blanks on the left.

2. Unblanking function

1. LTRIM () removes the space in the header of the string.

2. RTRIM () removes the space at the end of the string.

Third, take the substring function

1. Left () LEFT (,) returns integer_expression characters from the left of character_expression.

2. RIGHT () RIGHT (,) returns integer_expression characters from the right of character_expression.

3. SUBSTRING () SUBSTRING (, length) returns the part of the string that starts length from the first _ position character on the left of the string.

4. String comparison function

1. CHARINDEX () returns the starting position of a specified substring in the string. CHARINDEX (,) where substring _ expression is the character expression you are looking for, and expression can be a string or a column name expression. If no substring is found, a value of 0 is returned. This function cannot be used with TEXT and IMAGE data types.

2. PATINDEX () returns the starting position of a specified substring in the string. PATINDEX (,) must have a percent sign "%" before and after the neutron string expression, otherwise the return value is 0. Unlike the CHARINDEX function, wildcards can be used in substrings of the PATINDEX function, and this function can be used with CHAR, VARCHAR, and TEXT data types.

Fifth, string operation function

1. QUOTENAME () returns a string enclosed by a specific character. QUOTENAME ([, quote_ character]) where quote_ character indicates the character used in the bracketed string, and the default value is "[]".

2. REPLICATE () returns a string that repeats character_expression a specified number of times. REPLICATE (character_expression integer_expression) returns NULL if the integer_expression value is negative.

3. REVERSE () reverses the order of the characters of the specified string. REVERSE () where character_expression can be a string, a constant, or the value of a column.

4. REPLACE () returns the string with the specified substring replaced. REPLACE (,) replaces the substring string_expression2 in string_expression1 with string_expression3.

5. SPACE () returns a blank string of specified length. SPACE () returns NULL if the integer_expression value is negative.

6. STUFF () replaces the substring of the specified position and length with another substring.

STUFF (,) returns the NULL value if the starting position is negative or the length value is negative, or if the starting position is greater than the length of character_expression1. If the length length is greater than the length to the right of the start_ position in the character_expression1, the character_expression1 retains only the first character.

After reading the above, have you mastered the methods of the commonly used string handling functions in sql? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.