Domain Name interception by SQL

Summary of several methods for intercepting domain names using SQL statements. For more information, see

Summary of several methods for intercepting domain names using SQL statements. For more information, see

Recently, the domain name of the database needs to be rebalanced, because SQL is easy to use and can be sorted out.

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

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

------------------------------------
Result: http

B. Extract N characters from the right of the string (for example, www.jb51.net)

The Code is as follows:
Declare @ S1 varchar (100)
Select @ S1 = 'HTTP: // www.jb51.net'
Select right (@ S1, 11)

------------------------------------
Result: www.jb51.net

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.jb51.net'
Select SUBSTRING (@ S1, 8, 3)

------------------------------------
Result: www.jb51.net

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.jb51.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.jb51.net

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.jb51.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.jb51.net

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.jb51.net'
Select REPLACE (@ S1, 'HTTP ://','')

------------------------------------
Result: www.jb51.net

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.jb51.net'
Select STUFF (@ S1, CHARINDEX ('HTTP: // ', @ S1), Len ('HTTP ://'),'')

------------------------------------
Result: www.jb51.net

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:

The Code is as follows:
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.

The above methods can solve the basic problem, but when the domain name in the database itself is not the standard domain name overall (for example: http://a.com, http://a.com/a.aspx?http://www.a.com/www/a.aspx)
At this time, the interception of SQL is more complicated. The final key is to make good use of Substring and charindex functions, which can be basically solved.