Introduction to SQL truncation string functions

This article adds several common string truncation functions in msssql to your favorites. The following are examples. If you need them, refer to them.

This article adds several common string truncation functions in msssql to your favorites. The following are examples. If you need them, refer to them.

A. truncates N characters from the left of the string.

The Code is as follows:
Declare @ S1 varchar (100) Select @ S1 = 'HTTP: // www.111cn.net' Select Left (@ S1, 4) ------------------------------------ Result: http

B. Extract N characters starting from the right of the string (for example, www.163.com)

The Code is as follows:
Declare @ S1 varchar (100) Select @ S1 = 'HTTP: // www.163.com' Select right (@ S1, 11) ------------------------------------ Result: www.163.com

C. truncate any position and length of a string (for example, www)

The Code is as follows:
Declare @ S1 varchar (100) Select @ S1 = 'HTTP: // www.111cn.net' Select SUBSTRING (@ S1, 8, 3) ------------------------------------ Result: www

The above examples show the known truncation position and length. The following example shows the unknown position.

2. Intercept functions with unknown locations

A. Intercept the string after the specified string (for example, intercept the string after http)

Method 1:

The Code is as follows:
Declare @ S1 varchar (100) Select @ S1 = 'HTTP: // www.111cn.net' Select Substring (@ S1, CHARINDEX ('www ', @ S1) + 1, Len (@ S1 )) /* Here you can also write: Select Substring (@ S1, CHARINDEX ('//', @ S1) + 2, Len (@ S1 ))*/ ------------------------------------ Result: www.163.com

Note: The CHARINDEX function is case insensitive when searching strings. Therefore, CHARINDEX ('www ', @ S1) can also be written as CHARINDEX ('www', @ S1)

Method 2: (similar to method 1)

The Code is as follows:
Declare @ S1 varchar (100) Select @ S1 = 'HTTP: // www.111cn.net' Select Substring (@ S1, PATINDEX ('% www %', @ S1) + 1, Len (@ S1 )) -- This can also be written as follows: Select Substring (@ S1, PATINDEX ('% // %', @ S1) + 2, Len (@ S1 )) ------------------------------------ Result: www.163.com

The difference between the PATINDEX function and CHARINDEX function is that the former parameter can be used to increase the query function.

Method 3:

The Code is as follows:
Declare @ S1 varchar (100) Select @ S1 = 'HTTP: // www.111cn.net' Select REPLACE (@ S1, 'HTTP ://','') ------------------------------------ Result: www.163.com

Use the REPLACE function to REPLACE null characters except the strings to be displayed.

Method 4:

The Code is as follows:
Declare @ S1 varchar (100) Select @ S1 = 'HTTP: // www.111cn.net' Select STUFF (@ S1, CHARINDEX ('HTTP: // ', @ S1), Len ('HTTP ://'),'') ------------------------------------ Result: www.163.com

The difference between the function STUFF and REPLACE is that the former can specify the replacement range, while the latter can be replaced within the whole range.

B. Intercept the specified character string (for example, intercept the file name in C: Windowstest.txt)
Different from A, when the search object is not one, the method above can only search for the first position.

Method 1:

The Code is as follows:
Declare @ S1 varchar (100) Select @ S1 = 'C: Windowstest.txt' Right (@ S1, charindex ('', REVERSE (@ S1)-1) ------------------------------------- Result: text.txt

Use the REVERSE function to obtain the length of the string to be truncated.