SQL Server CHARINDEX functions and PATINDEX functions (GO)

The

Charindex and PATINDEX functions are often used to search for characters or strings in a segment of a character. If the character being searched contains a character to search for, then the two functions return a nonzero integer, which is the beginning of the character to be searched in the character being searched. The PATINDEX function supports searching with wildcards, whereas wildcard characters are not supported by CHARINDEX. Next, we analyze the two functions one by one.

How to use the CHARINDEX function
       charindex function to return the starting position of a character or string within another string. The CHARINDEX function call method is as follows:
       charindex (expression1, expression2 [, Start_ Location])
       expression1 is the character to look for in expression2, Start_ Location is where the CHARINDEX function begins to find expression1 in expression2. The
       charindex function returns an integer that is the position of the string to find in the string being searched for. If CHARINDEX does not find the string to find, then the function integer "0". Let's take a look at the results of the following function commands:
      charindex (SQL, Microsoft SQL Server)
       This function command returns the starting position of SQL in Microsoft SQL Server, in which case the CHARINDEX function returns the location of "s" in "Microsoft SQL Server" 11.
Next, let's look at this charindex command:
      charindex (7.0, Microsoft SQL Server)

In this example, CHARINDEX returns zero because the string "7.0" cannot be found in Microsoft SQL Server.

Next, let's take a look at two examples of how to use the CHARINDEX function to solve the actual T-SQL problem.

The first example assumes that you want to display the last name of the first 5 rows of the contact column of the Northwind database Customer table. This is the first 5 rows of data
ContactName
------------------------------
Maria Anders
Ana Trujillo
Antonio Moreno
Thomas Hardy
Christina Berglund
As you can see, Customname contains the first name and last name of the customer, separated by a space between them. I use the CHARINDX function to determine the position of the middle space of two names. In this way, we can analyze the space position of the ContactName column so that we can display only the last name part of the column. This is the last name record for the first 5 lines of the Customer table that displays Northwind!
Select Top 5 substring (Contactname,charindex (", ContactName) +1, Len (ContactName)) as [last name] from Northwind.dbo.customers

SELECT SUBSTRING (' Lipasdas lip Asdfa ', CHARINDEX (' lip ', ' Lipasdas lip Asdfa ') +3,2)
The following is the result of this command output.
Last Name
------------------------------
Anders
Trujillo
Moreno
Hardy
Berglund
The CHARINDEX function finds the space between first name and last name, so the SUBSTRING function can separate the ContactName column so that only the last name is selected. I add 1 to the integer returned by the CHARINDEX function so that last name does not start with a space.
In the second example, it is said that you want to calculate the number of records in which a field contains a specific character. The CHARINDEX function is a convenient solution to your problem. Calculates the number of records in the Addresses field in the Northwind.dbo.customer table that contain the word road or his abbreviation Rd, similar to the SELECT statement:
Select COUNT (*) from Northwind.dbo.customers

where CHARINDEX (rd,address) > 0 or CHARINDEX (road,address) > 1

How to use the PATINDEX function
The PATINDEX function returns the starting position of a character or string in another string or expression, and the PATINDEX function supports the use of wildcards in the search string, which makes the PATINDEX function valuable for a changing search string. The command for the PATINDEX function is as follows:
Patindex (%pattern%, expression)
Pattern is the string you are searching for, and expression is the string to be searched. In general, expression is a field in a table that needs to be marked with a "%" before and after the pattern, unless you search for a string that is in the front or last face of the string being shrunk.
As with the CHARINDEX function, the PATINDEX function returns the starting position of the search string in the searched string. If there is such a patindex function:
Patindex (%BC%,ABCD)
The PATINDEX function returns a result of 2, which is the same as the CHARINDEX function. The% mark here tells the PATINDEX function to find the string "BC", regardless of the number of characters in the string being searched before and after "BC"!
If you want to know whether the searched string starts with a specific string, you can omit the previous% mark. The patinded function should be written like this:
Patindex (AB%,ABCD)
The result of this command execution returns 1, indicating that the searched string "AB" is found in the string being searched for "ABCD".
Using wildcards allows you to edit a much more complex search string than the simple example I've raised above. If you want to determine whether a string contains letters A and z, and any number, the Parindex function command might look like this:
Patindex (%[a,z,0-9]%[a,z,0-9]%[a,z,0-9]%,xyzabc123)
Note that the search Character section in the above example uses a lot of wildcard characters. View SQL Server Books Online to get more information about wildcard characters. Next, let's look at two examples of how patindex and select work together.
Suppose you want to find out any record in the Description field in the Northwind.dbo.categories table that contains the word "bread" or "bread", then the SELECT statement might look like this:
Select description from Northwind.dbo.categories
where PATINDEX (%[b,b]read%,description) > 0
Here I use wildcard characters to determine the uppercase and lowercase "b". After I execute this script in the Notthwind database, I get the following result:
Description
--------------------------------------------------------
Desserts, candies, and sweet breads
Breads, crackers, pasta, and cereal
This is another example of using an additional wildcard to find some records. This example is how to select the above query results, the second sub-letter of the Description field is not the "E" record.
Select description from Northwind.dbo.categories
where PATINDEX (%[b,b]read%,description) > 0
and Patindex (_[^e]%,description) = 1
By adding a patindex function with the ^ wildcard in the conditional statement, we can filter out the record "dessert, candies, and sweet breads". The above query results have only one record.
Description
--------------------------------------------------------
Breads, crackers, pasta, and cereal
Summarize
You can now find the distinction between charindex and patindex searching for strings. The PATINDEX function supports the use of wildcards, which can be used in many different lookups. and charindex not enough. Depending on your own situation, these two functions are useful for searching, controlling, and parsing your strings in SQL Server.

SQL Server CHARINDEX functions and PATINDEX functions (GO)