SQL string Functions Encyclopedia and how to use examples _mssql

String function contents supported by SQL Server:

Copy Code code 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) function
ASCII (String) function

(1) LEN (string) function: This function is used to calculate the length of a string, accept a parameter (can be a string field in the table or table, or else). In this case, there is no case-sensitive (the following functions are the same). Len,len, or Len are equal. Examples are as follows:

SELECT FName, LEN (FName) from T_person
Note: If the parameter passed to the Len function is a time field, then the return result is incorrect, such as a DateTime. So, this function is used to compute the length of the string, and other type parameters can return the result, but not necessarily correct.

I, if the pass is null, then return or null.

II, for a string shape like ' A a ', the result returned is 4, not 5. That is, the calculated result does not include a space string portion on the right.

(2) LOWER (string) function: This function is used to convert a string to a lowercase string. As with the Len () function, a parameter is also accepted.

Copy Code code as follows:

SELECT FName, LOWER (FName) from T_person

Note: If the pass is null, then the return is null.

(3) UPPER (string) function: In contrast to the lower () function, this function converts a string to an uppercase string. Also accepts an argument.

Copy Code code as follows:

SELECT FName, UPPER (FName) from T_person

Note: If the pass is null, then the return is null.

(4) LTRIM (string) function: This function is to remove the space on the left side of the string (there is nothing for the space inside the string). Also accepts an argument.

Copy Code code as follows:

SELECT Fname,ltrim (FName), LTRIM (' abc ') from T_person

Note: If the pass is null, then the return is null.

(5) RTRIM (string) function: This function is to remove the space on the right side of the string (there is nothing to do with the space in the string). Also accepts an argument.

Copy Code code as follows:

SELECT Fname,rtrim (FName), RTRIM (' abc ') from T_person

Note: If the pass is null, then the return is null.

If you need to remove both sides of the space (for the space in the string is powerless), you need to use the Union.

Copy Code code 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 get substrings. Where the argument string is the primary string, the start_position is the starting position of the substring in the main string, length
is the maximum length of a substring. Note that the start_position here is starting at 1, unlike arrays that start at 0. If a 0 is given, it is equivalent to taking a ".

Copy Code code as follows:

SELECT FName, SUBSTRING (fname,2,3) from T_person

Note: If the pass is null, then the return is null.

(7) CHARINDEX (substring,string) function: This function is the position of the computed substring in the main string. Where the parameter substring is a substring, string is the primary string. This function can detect whether a drawn substring exists in the main string, and if so, return to its location. If there is a match, the result is greater than 0. That is, the match is successful, at least starting from 1.

Copy Code code as follows:

SELECT fname,charindex (' m ', FName), CHARINDEX (' ly ', FName)
From T_person

Note: If the pass is null, then the return is null.

(8) Left (string,length) function: This function is the implementation of the start of the substring from the left-hand side, where the parameter string is the main string, length is the maximum length of the substring. The resulting result is a substring within the range of 1 to length.

Copy Code code as follows:

SELECT FName, Left (fname,3), left (fname,2)
From T_person

Note: If the pass is null, then the return is null.

This approach equates to using the substring (string,start_position,length) function:

Copy Code code as follows:

SELECT fname,substring (FName, 1,3) from T_person

(9) Right (string,length) function: This function is to implement a substring from the right-hand side, where the argument string is the primary string, and length is the maximum length of the substring. The resulting result is a substring within the range of 1 to length.

Copy Code code as follows:

SELECT FName, right (fname,3), right (fname,2)
F

ROM T_person

Note: If the pass is null, then the return is null.

This approach equates to using the substring (string,start_position,length) function, where substring (string, LEN (String)-length+1, length) is equivalent to right ( String,length).

Copy Code code as follows:

SELECT FName, SUBSTRING (Fname,len (FName) -2,3), SUBSTRING (Fname,len (FName) -1,2)
From T_person

(a) Replace (string,string_tobe_replace,string_to_replace) function: This function is the replacement function of a string, where the parameter string is the main string to be substituted, and the parameter string_ Tobe_replace is the string to be replaced, that is, String_to_replace replaces all occurrences in the string_tobe_replace.

Copy Code code as follows:

Select Fname,replace (FName, ' I ', ' e '), Fidnumber,
REPLACE (Fidnumber, ' 2345 ', ' ABCD ') from T_person

To implement the ability to delete a string by substituting ' ':

Copy Code code as follows:

SELECT FName, REPLACE (FName, ' m ', '), Fidnumber,
REPLACE (Fidnumber, ' 123 ', ') from T_person

The LTrim (string) function and the RTrim (string) function are described earlier, which can ultimately remove both sides of the space, but not the space within the string. The Replace function can be easily resolved.

Copy Code code as follows:

SELECT replace (' ABC 123 WPF ', ', ', '), REPLACE (' CCW enet WCF F ', ', ')

(one) ASCII (string) function: This function is used to get the ASCII code of a character that has a single parameter, which is the character for the ASCII code, and the function returns the ASCII code of the first character if the argument is a string.

Copy Code code as follows:

SELECT ASCII (' a '), ASCII (' abc ')

Note: If the pass is null or "", then the return is null.

() CHAR (string) function: In contrast to (11), this function is used to get the ASCII code of a character.

Copy Code code as follows:

SELECT char (in), char (M), ' a ', char (ASCII (' a '))

Note: If the pass is null, then the return is null.

Difference (string) function: This function is used to compare the pronunciation similarity of two strings, it calculates the pronunciation eigenvalues of two strings, compares them, and then returns a value from 0 to 4 to reflect the pronunciation similarity of two strings, The larger the value, the greater the pronunciation similarity of the two strings.

Copy Code code as follows:

SELECT difference (FName, ' Merry ') from T_person

Note: If the pass is null, then the return is null.