SQL string functions and usage examples

Content of string functions supported by SQL SERVER:

Copy codeThe Code is as follows:
LEN (string) Function
LOWER (string) Function
UPPER (string) Function
LTRIM (string) Function
RTRIM (string) Function
SUBSTRING (string, start_position, length) Function
CHARINDEX (string, substring) Function
LEFT (string, length) Function
RIGHT (string, length) Function
ASCII (string) Functions
ASCII (string) Functions

(1) LEN (string) function: This function is used to calculate the length of a string and accept a parameter (it can be a string field in the table or another field ). It is case-insensitive (the following functions are the same ). LEN, len, or Len are equivalent. Example:

SELECT FName, LEN (FName) FROM T_Person
Note: If the parameter passed to the LEN function is a time field, the returned result is incorrect, for example, a DateTime. Therefore, this function is used to calculate the length of a string. Other types of parameters can return results, but it is not necessarily correct.

I. If NULL is input, NULL is returned.

II. For a string like 'a A', the returned result is 4 instead of 5. That is, the calculated result does not include all spaces on the right.

(2) LOWER (string) function: This function is used to convert all strings to lowercase strings. Like the LEN () function, it also accepts a parameter.
Copy codeThe Code is as follows:
SELECT FName, LOWER (FName) FROM T_Person

NOTE: If NULL is input, NULL is returned.

(3) UPPER (string) function: opposite to the LOWER () function, this function converts all strings to uppercase strings. A parameter is also accepted.
Copy codeThe Code is as follows:
SELECT FName, UPPER (FName) FROM T_Person

NOTE: If NULL is input, NULL is returned.

(4) LTRIM (string) function: This function removes spaces on the left of the string (there is nothing to do with spaces in the string ). A parameter is also accepted.
Copy codeThe Code is as follows:
SELECT FName, LTRIM (FName), LTRIM ('abc') FROM T_Person

NOTE: If NULL is input, NULL is returned.

(5) RTRIM (string) function: This function removes spaces on the right of the string (there is nothing to do with spaces in the string ). A parameter is also accepted.
Copy codeThe Code is as follows:
SELECT FName, RTRIM (FName), RTRIM ('abc') FROM T_Person

NOTE: If NULL is input, NULL is returned.

If you need to remove spaces on both sides (there is nothing to do with spaces in the string), you need to use them together.
Copy codeThe Code is as follows:
SELECT FName, LTRIM (RTRIM (FName), LTRIM (RTRIM ('abc') FROM
T_Person

(6) SUBSTRING (string, start_position, length) function: This function is used to obtain the SUBSTRING. Here, the parameter string is the main string, and start_position is the starting position of the sub-string in the main string, length
The maximum length of a substring. Note that start_position starts from 1, which is different from that of array starting from 0. If 0 is given, it is equivalent to taking ''.
Copy codeThe Code is as follows:
SELECT FName, SUBSTRING (FName, 2, 3) FROM T_Person

NOTE: If NULL is input, NULL is returned.

(7) CHARINDEX (substring, string) function: This function is used to calculate the position of a substring in the main string. Here, the substring parameter is a sub-string, and the string is the main string. This function can detect whether the specified sub-string exists in the main string. If yes, it can return the position. If a match exists, the result is greater than 0. That is, if the match is successful, it must start at least 1.
Copy codeThe Code is as follows:
SELECT FName, CHARINDEX ('M', FName), CHARINDEX ('ly ', FName)
FROM T_Person

NOTE: If NULL is input, NULL is returned.

(8) LEFT (string, length) function: This function is used to obtain the substring from the LEFT. The parameter string is the main string, and the length is the maximum length of the substring. That is, the obtained result is a substring within the range of 1 to length.
Copy codeThe Code is as follows:
SELECT FName, LEFT (FName, 3), LEFT (FName, 2)
FROM T_Person

NOTE: If NULL is input, NULL is returned.

This method is equivalent to using the SUBSTRING (string, start_position, length) function:
Copy codeThe Code is as follows:
SELECT FName, SUBSTRING (FName, 1, 3) FROM T_Person

(9) RIGHT (string, length) function: This function is used to obtain the substring from the RIGHT. The parameter string is the main string, and the length is the maximum length of the substring. That is, the obtained result is a substring within the range of 1 to length.
Copy codeThe Code is as follows:
SELECT FName, RIGHT (FName, 3), RIGHT (FName, 2)
FROM T_Person

NOTE: If NULL is input, NULL is returned.

This method is equivalent to using the SUBSTRING (string, start_position, length) function, where SUBSTRING (string, LEN (string)-length + 1, length) is equivalent to RIGHT (string, length ).
Copy codeThe Code is as follows:
SELECT FName, SUBSTRING (FName, LEN (FNAME)-2, 3), SUBSTRING (FName, LEN (FNAME)-1, 2)
FROM T_Person

(10) REPLACE (string, string_tobe_replace, string_to_replace) function: This function is used to REPLACE strings. The string parameter is the main string to be replaced, the string_tobe_replace parameter is the string to be replaced. That is, the string_to_replace parameter replaces all occurrences of string_tobe_replace.
Copy codeThe Code is as follows:
Select FName, REPLACE (FName, 'I', 'E'), FIDNumber,
REPLACE (FIDNumber, '123', 'abcd') FROM T_Person

You can delete a string by replacing it:
Copy codeThe Code is as follows:
SELECT FName, REPLACE (FName, 'M', ''), FIDNumber,
REPLACE (FIDNumber, '123', '') FROM T_Person

Previously, we introduced the LTRIM (string) function and the RTRIM (string) function. The two functions can only delete spaces on both sides, but there is nothing to do with spaces in the string. With the REPLACE function, you can easily solve the problem.
Copy codeThe Code is as follows:
Select replace ('abc 123 wpf ', '',''), REPLACE ('ccw enet wcf F ','','')

(11) ASCII (string) function: This function is used to obtain the ASCII code of a character. It has only one parameter. this parameter is the character to be ASCII, if the parameter is a string, the function returns the ASCII code of the first character.
Copy codeThe Code is as follows:
Select ascii ('A'), ASCII ('abc ')

NOTE: If NULL or ''is input, NULL is returned.

(12) CHAR (string) function: opposite to (11), this function is used to obtain the ASCII code of a character.
Copy codeThe Code is as follows:
Select char (56), CHAR (90), 'A', CHAR (ASCII ('A '))

NOTE: If NULL is input, NULL is returned.

(13) DIFFERENCE (string) function: This function is used to compare the pronunciation similarity between two strings. It can calculate the pronunciation feature values of the two strings and compare them, then, a value between 0 and 4 is returned to reflect the pronunciation similarity between the two strings. The larger the value, the greater the pronunciation similarity between the two strings.
Copy codeThe Code is as follows:
Select difference (FName, 'merry') FROM T_Person

NOTE: If NULL is input, NULL is returned.