String processing functions

This article mainly summarizes the string processing functions that are frequently used in the development process. They are very useful when processing strings. The following functions are provided in summary.

1. String concatenation operator

2. substring Extraction

3, left and right

4, Len and datalength

5. charindex Function

6. patindex Function

7. Replace

8. replicate the copied string.

9. Stuff Function

10, upper and lower functions

11. rtrim and ltrim Functions

String concatenation operator

Due to business needs, sometimes we need to combine two fields (columns), add a separator in the middle, and then output. Then we will use the Character String concatenation operator [+. For example, we concatenate the firstname, space, and lastname columns in the Employees table to generate the complete name fullname column.

SQL query code:

-- Set the database context use tsqlfundamentals2008; go -- fullname is the result of concatenating operators. Select empid, firstname, lastname, firstname + n'' + lastname as fullname from HR. Employees

Query results:

It should be noted that ansi SQL requires that a Series Operation on null values also produces the result of null values, which is the default behavior of SQL Server. Of course, you can change the default SQL server processing method by setting the session option "concat_null_yields_null" to "off", but remember to set it back to the original on state after processing.

Substring

The substring function is used to extract substrings from strings. For example, the following code returns the 'abc' string '.

SQL query code:

SELECT SUBSTRING(‘abcde‘,1,3);

Query results:

Note: 1. Generally, the starting position starts from 1.

2. If the sum of the second and third parameters exceeds the length of the entire string, the function returns the entire string starting from the starting position until the end of the string without causing an error. You can specify a very large value or a value that represents the length of the entire string to return all content starting from a position until the end.

Left and right

The left and right functions are short form of substring. They return the specified number of characters from the left or right of the input string. For example, the following code returns the character 'cde '.

SQL query code:

SELECT RIGHT(‘abcde‘,3);

The query result is the same as that of substring. Left is the same as right.

Len and datalength

The LEN function returns the number of characters in the input string. The datalength function returns the number of bytes of the input string. Pay attention to their differences. Len Syntax: Len (string), datalength Syntax: datalength (string)

For example, the following code returns a string of 5 Characters

SQL query code:

SELECT LEN(N‘abcde‘);

Query result output: 5

If the datalength function is used, the output is 10.

Charindex Function

The charindex function returns the starting position of the first occurrence of a substring in a string. Its Syntax format is charindex (substring, string [, start_pos]). This function searches for the first parameter (substring) in the second parameter (string ), the start position is returned. You can select the third parameter (start_pos) to tell the function where to start searching from the string. If this parameter is not specified, search from the first string of the string. If the substring cannot be found in the string, the function returns 0.

For example, the following code finds the position of the first space in 'trac mcgrady' and returns 5

SQL query code:

SELECT CHARINDEX(‘ ‘,‘trac mcgrady‘);

Patindex Function

The patindex function returns the starting position of the first occurrence of a pattern in a string. Its Syntax format is: patindex (pattern, string)

For example, we need to find the position where the number appears for the first time in the string.

SQL query code:

SELECT PATINDEX(‘%[0-9]%‘,‘abcd123efgh‘);

Query Result: 5

Replace

The replace function replaces a substring in a string with another one. Its Syntax format is: Replace (string, substring1, substring2). This function replaces all substring1 in string with substring2.

For example, the following code replaces all the hyphens (-) in the input string with colons (:)

SQL query code:

SELECT REPLACE(‘1-a 2-b‘,‘-‘,‘:‘);

Query Result: 1: A 2: B

Replicate the copied string

The replicate function copies the string value for a specified number of times. Its Syntax format is: Replicate (string, n)

For example, the following code copies the string 'abc' three times and returns the string 'abcabcabc '.

SQL query code:

SELECT REPLICATE(‘abc‘,3);

Query Result: 'abcabcabc'

The following example shows the replicate function and the use of the right function and String concatenation. The following query of production. Suppliers generates a 10-digit string representation for each supplier's integer ID (when less than 10 digits are entered, '0' is prefixed ')

SQL query code:

-- Set database context use cases; goselect supplierid, right (replicate ('0', 9) + Cast (supplierid as varchar (10), 10) as strsupplieridfrom production. suppliersorder by supplierid

Query results:

Stuff Function

The stuff function can delete a substring in the string and then insert a new substring to replace it. Its Syntax format is: stuff (string, POs, delete_length, insertstring)

For example, the following code processes the string 'xyz', first deletes the second character, and then inserts the string 'abc '.

SQL query code:

SELECT STUFF(‘xyz‘,2,1,‘abc‘);

Query Result: 'xabcz'

Upper and lower functions

The upper and lower functions are used to convert all characters in the input string into uppercase or lowercase letters. Their syntax format is: Upper (string), lower (string ).

For example, the first function returns 'trac mcgrady', and the second function returns 'trac mcgrady '.

-- Returns 'trac mcgrady' select upper ('trac mcgrady'); -- returns 'trac mcgrady' select lower ('trac mcgrady ');

Rtrim and ltrim Functions

The rtrim and ltrim functions are used to delete trailing spaces and leading spaces of input strings. Their syntax format is rtrim (string) and ltrim (string ). If you want to delete both leading and trailing spaces, you can use the result of one function as the input of another function. For example, the following code deletes leading and trailing spaces of the input string and returns 'abc'

SQL query code:

-- Return 'abc' select rtrim (ltrim ('abc '));