SQL truncation string function sharing

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

Declare @ S1 varchar (100)
Select @ S1 = 'HTTP: // www.xrss.cn'
Select Left (@ S1, 4)
------------------------------------
Result: http


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

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)

Declare @ S1 varchar (100)
Select @ S1 = 'HTTP: // www.xrss.cn'
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:

Declare @ S1 varchar (100)
Select @ S1 = 'HTTP: // www.xrss.cn'
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)

Declare @ S1 varchar (100)
Select @ S1 = 'HTTP: // www.xrss.cn'
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:

Declare @ S1 varchar (100)
Select @ S1 = 'HTTP: // www.xrss.cn'
Select REPLACE (@ S1, 'HTTP ://','')
------------------------------------
Result: www.163.com


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

Method 4:

Declare @ S1 varchar (100)
Select @ S1 = 'HTTP: // www.xrss.cn'
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: \ Windows \ test.txt)
Different from A, when the search object is not one, the method above can only search for the first position.

Method 1:

Declare @ S1 varchar (100)
Select @ S1 = 'C: \ Windows \ test.txt'
Select right (@ S1, charindex ('\', REVERSE (@ S1)-1)
-------------------------------------
Result: text.txt

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