SQL Server String Functions

This article is a transcript of the programmer's SQL code, which will explain the string functions of SQL Server.

In fact, the database, like the Program language library, will integrate many APIs that can be used. These APIs, if you are familiar with it, will reduce the processing operations at the code level.

I think the use of string functions is still very high, so I think while learning to write down the common things. To deepen their own understanding, and secondly through the induction of knowledge for my use.

SQL Server-supported string functions (other databases are not listed here, want to see more attention to the "Programmer's SQL Code"), this article will introduce the following:

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.

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.

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.

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.

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.

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 ".

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.

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.

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:

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