SQL intercept string function sharing _mssql

A. Intercept start n characters from the left side of the string

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


B. Intercepting n characters starting from the right of the string (for example, character www.163.com)

Declare @S1 varchar (100)
Select @S1 = ' http://www.163.com '
Select Right (@S1, 11)
------------------------------------
Display results: www.163.com


C. Intercept arbitrary position and length in a string (e.g., character www)

Declare @S1 varchar (100)
Select @S1 = ' http://www.xrss.cn '
Select SUBSTRING (@S1, 8, 3)
------------------------------------
Display results: www

All of the above examples are known to intercept locations and lengths, and here are examples of unknown locations

2. function to intercept unknown position


A. Intercepting the string after the specified string (for example, intercepting the string following the http://)

Method One:

Declare @S1 varchar (100)
Select @S1 = ' http://www.xrss.cn '
Select Substring (@S1, CHARINDEX (' www ', @S1) +1,len (@S1))
* This can also be written here: Select Substring (@S1, CHARINDEX ('//', @S1) +2,len (@S1)) * *
------------------------------------
Display results: www.163.com

Note: The CHARINDEX function searches for strings that are case-insensitive, so charindex (' www ', @S1) can also be written as charindex (' www ', @S1)

Method Two: (similar to the method one)

Declare @S1 varchar (100)
Select @S1 = ' http://www.xrss.cn '
Select Substring (@S1, PATINDEX ('%www% ', @S1) +1,len (@S1))
It can also be written here: Select Substring (@S1, PATINDEX ('%//% ', @S1) +2,len (@S1))
------------------------------------
Display results: www.163.com


The difference between function Patindex and Charindex is that the former can parameter some parameters and increase the function of the query.

Method Three:

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


Replace the character with a character substitution, replacing the characters that need to display the string with NULL

Method Four:

Declare @S1 varchar (100)
Select @S1 = ' http://www.xrss.cn '
Select STUFF (@S1, CHARINDEX (' http://', @S1), Len (' http://'), "
------------------------------------
Display results: www.163.com

The difference between function stuff and replace is that the former can specify a replacement range, while the latter is full range substitution

B. Intercepting the string after the specified character (for example, intercepting the filename in C:\Windows\test.txt)
Unlike a, when a search object is not one, you can use the above method to search only the first location

Method One:

Declare @S1 varchar (100)
Select @S1 = ' C:\Windows\test.txt '
Select Right (@S1, charindex (' \ ', REVERSE (@S1))-1)
-------------------------------------
Display results: Text.txt

Using function reverse to get the length of the string to intercept