SQL intercepts the domain name problem _mssql

Recently because of the domain name of the database to row heavy, because the SQL directly used to facilitate a few, just tidy up

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

Copy Code code as follows:

Declare @S1 varchar (100)
Select @S1 = ' http://www.jb51.net '
Select Left (@S1, 4)

------------------------------------
Display results: http

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

Copy Code code as follows:

Declare @S1 varchar (100)
Select @S1 = ' http://www.jb51.net '
Select Right (@S1, 11)

------------------------------------
Display results: www.jb51.net

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

Copy Code code as follows:

Declare @S1 varchar (100)
Select @S1 = ' http://www.jb51.net '
Select SUBSTRING (@S1, 8, 3)

------------------------------------
Display results: www.jb51.net

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:

Copy Code code as follows:

Declare @S1 varchar (100)
Select @S1 = ' http://www.jb51.net '
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.jb51.net

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)

Copy Code code as follows:

Declare @S1 varchar (100)
Select @S1 = ' http://www.jb51.net '
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.jb51.net

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

Method Three:

Copy Code code as follows:

Declare @S1 varchar (100)
Select @S1 = ' http://www.jb51.net '
Select REPLACE (@S1, ' http://', ')

------------------------------------
Display results: www.jb51.net

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

Method Four:

Copy Code code as follows:

Declare @S1 varchar (100)
Select @S1 = ' http://www.jb51.net '
Select STUFF (@S1, CHARINDEX (' http://', @S1), Len (' http://'), "

------------------------------------
Display results: www.jb51.net

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:

Copy Code code as follows:

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

These methods above can solve the basic problem, but when the domain name in the database is not the standard domain name as a whole (example: http://a.com, http://a.com/a.aspx, http://www.a.com/www/a.aspx)
This time the interception of the SQL will be more complex, the final key is to make good use of substring,charindex two functions, basically can be solved