Usage of MSSQL substring and CHARINDEX

Examples used in the work:

SELECT *
from [CSGDC. DATAETLDB]. [dbo]. [Strategieslist] where strategy_name like '% infrastructure system% '
and SUBSTRING (Strategy_name,charindex (' _ ', strategy_name,12) +1,100) in
(Select SUBSTRING (Strategy_name,charindex (' _ ', strategy_name,12) +1,100) as Strategy_name1
from [CSGDC. DATAETLDB]. [dbo]. [Strategieslist] where strategy_name like '% infrastructure system% ' GROUP by
SUBSTRING (Strategy_name,charindex (' _ ', strategy_name,12) +1,100)
Have count (1) >1) and previous_sql is null

from:http://blog.csdn.net/yhc0322/article/details/7073953

Suppose a string is the email address "[email protected]" and the last string to fetch should be "hello".

Because of the above requirement, the knowledge of the string function in the database is looked at, and the following methods can be solved in the end:

One, the function used: substring (",,), CHARINDEX (", ")

Select SUBSTRING ('[email protected]', 1,charindex (' @ ', '[email protected]')-1)

1.substring (string expression, start position, length):

Intercepts the length of the character from the specified position of a specified string;

The first parameter represents the truncated string;

The second parameter represents the position to start the intercept in the first argument;

The third parameter represents the length to intercept.

Example: Select substring (' abc123 ') → return AB

Starting from the first character of the string ' abc123 ', a total of two characters are intercepted, and finally ' AB ' is obtained.

2.charindex (string expression 1, string expression 2[, integer expression]):

In string 2, look for the string 1 if there is a return of the first matching position if there is no return 0. Returns null if there is a null in string 1 and string 2.

You can specify the starting position of the lookup in string 2.

Example: Select CHARINDEX (' ab ', ' bcabtabd ') → return 3

Select CHARINDEX (' ab ', ' bcabtabd ', 4) → return 6

Second, the function used: Left (","), CHARINDEX (",")

Select Left ('[email protected]', charindex (' @ ', '[email protected]')-1)

1.left (string-expression, integer-expression):

An integer expression character is truncated from the leftmost side of the string expression.

Example: Select Left (' ABCdef ', 3) → Return to ABC

Usage of MSSQL substring and CHARINDEX