SQL Server charindex and patindex detailed

server| detailed

SQL Server charindex and patindex detailed

If you have written many programs, you may occasionally encounter the need to determine whether a character or character string is included in a text, and in this article I will discuss using the CHARINDEX and PATINDEX functions to search for text columns and strings. I'll tell you how these two functions work, explaining their differences. At the same time provide some examples, through these examples, you can consider using these two functions to solve many different character search problems.
The CHARINDEX and PATINDEX functions are often used to search for characters or strings in a single word. If the character being searched contains a character to search for, then the two functions return a Non-zero integer that is the number of digits in the searched character that the character is searching for. The PATINDEX function supports searching using wildcard characters, but Charindex does not support wildcards. Next, we analyze the two functions individually.

How to use the CHARINDEX function

The CHARINDEX function returns the starting position of a character or string in another string. The CHARINDEX function calls the following methods:

CHARINDEX (expression1, expression2 [, Start_location])

Expression1 is the character to look for in expression2, start_location is the CHARINDEX function that begins to find expression2 in expression1.

The CHARINDEX function returns an integer that returns an integer that is the position of the string to find in the string being searched. 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 command execution:

CHARINDEX (' sql ', ' Microsoft SQL Server ')

This function command will return to the start of SQL in Microsoft SQL Server, in which case the CHARINDEX function will return "S" to position 11 in Microsoft SQL Server.
Next, let's look at this charindex command:

CHARINDEX (' 7.0 ', ' Microsoft SQL Server 2000 ')

In this example, CHARINDEX returns zero because the string "7.0" cannot be found in Microsoft SQL Server. Here are two examples to see 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 line of contact columns in the Northwind database Customer table. This is the first 5 lines of data
ContactName
------------------------------
Maria Anders
Ana Trujillo
Antonio Moreno
Thomas Hardy
Christina Berglund

As you can see, the customname contains the customer's name of the ' name ' and ' last ', separated by a space. 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 record for the first 5 lines of the Customer table showing Northwind!

Select Top 5 substring (contactname,charindex (', ContactName) +1,
Len (ContactName)) as [last Name] from Northwind.dbo.customers

The following is the result of this command output.
Last Name
------------------------------
Anders
Trujillo
Moreno
Hardy
Berglund

The CHARINDEX function finds a space between the name of a and last, 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 the last name does not start with a space.

In the second example, if you want to calculate a record, the number of records in a field that contains a particular character. The CHARINDEX function can solve your problem conveniently. Calculates the number of records in the Addresses field in the Northwind.dbo.Customer table that contain the word road or its abbreviated RD, which is similar to the selection 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 wildcard characters in the search string, which makes the PATINDEX function valuable for the variable search string. The commands for the PATINDEX function are as follows:

PATINDEX ('%pattern% ', expression)

Pattern is the string you are searching for, expression is the string you are searching for. In general, expression is a field in a table that needs to be labeled "%" before and after pattern, unless you are searching for a string that is at the front or end of the shrinking string.

Like 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 result of this patindex function is 2, which is the same as the CHARINDEX function. The% tag here tells the PATINDEX function to find the string "BC", regardless of the number of characters in the searched string before and after "BC"!
If you want to know if the searched string starts with a specific string, you can omit the% mark above. The patinded function should write this:

PATINDEX (' ab% ', ' ABCD ')

The result of this command returns 1, which means that the search string "AB" is found in the searched string "ABCD".

Using wildcards, you can edit a search string that is much more complex than the simple example I mentioned above. If you want to determine whether a string contains letters A and z, and any numbers, this parindex function command might look like this: