If you write a lot of programs, you may occasionally encounter to determine whether a character or character channeling is included in a piece of text, in this article I will discuss using the CHARINDEX and PATINDEX functions to search for text columns and strings. I'll show you how these two functions work, explaining their differences. While providing some 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 word. If the character being searched contains a character to search for, 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 using wildcards, while CHARINDEX does not support wildcard characters. Next, we analyze the two functions one by one.
How to use the CHARINDEX function
The CHARINDEX function returns 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 expression2 in expression1.
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 result of the following function command execution:
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 "S" in Microsoft SQL Server location 11.
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. Next, let's take a look at two examples of how to use the CHARINDEX function to solve the actual T-SQL problem.
For the first example, suppose 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 customer's first name and last name, which are separated by a space. I use the CHARINDX function to determine the position of the middle space of two names. With this method, 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
The following is the result of this command output.
Last Name
------------------------------
Anders
Trujillo
Moreno
Hardy
Berglund
(go) usage of CHARINDEX in SQL statements