MSSQL中使用子字符串查找的实现（mssql 子字符串查找）

Substring Location in MSSQL

In Microsoft SQL Server, a substring is a portion of a character string, typically consisting of one or more characters. It may be used to select some or all characters in a string. You can get the start or location of a substring within a character string in Microsoft SQL Server using the charindex() function. The charindex() function searches a certain character string (called the search string) within another character string and returns the start location of the search string, or 0 if the search string characters cannot be found.

For example, you may have a character string “Microsoft SQL Server” and you want to find the starting position of the substring “SQL”. The following statement can be used:

SELECT CHARINDEX(‘SQL’, ‘Microsoft SQL Server’)

The above statement returns 10 because the keyword “SQL” is located at the 10th character in the character string.

The charindex() function also works with Unicode and extended ASCII characters.

The charindex() function provides several additional argument options for more advanced substring query/search. For example, the third argument of the charindex() function specifies the starting position in the character string from which the search begins. The following statement returns the location of the substring “SQL” starting from the 11th character:

SELECT CHARINDEX(‘SQL’, ‘Microsoft SQL Server’, 11)

The fourth argument allows the substring search case to be specified. It is set to false by default, which means that case is ignored. To perform a case-sensitive search, it should be set to true.

SELECT CHARINDEX(‘SQL’, ‘Microsoft SQL Server’, 0, true)

The charindex() function can be used with other string manipulation functions to perform various tasks such as extracting a substring of a larger string. It can also be used in combination with other data manipulation language (DML) statements such as SELECT, UPDATE, and DELETE to perform complex operations on string data.

In summary, the charindex() function allows a substring location to be quickly and easily retrieved in MSSQL. It provides both basic and advanced options for substring string manipulation.