Usage of CHARINDEX in SQL statements

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

Charindex Function Introduction
First, the grammar
CHARINDEX (CHAR1, string1 [, start_location])
If one of the char1 or string1 is a Unicode data type (nvarchar or nchar) and the other is not, convert the other to a Unicode data type. CHARINDEX cannot be used with the text, ntext, and image data types.
If one of the CHAR1 or string1 is null, and the database compatibility level is 70 or higher, CHARINDEX will return null. If the database compatibility level is 65 or lower, CHARINDEX returns a null value only if both CHAR1 and string1 are null.
If string1 is not found within CHAR1, CHARINDEX returns 0.
Char1 An expression that contains the sequence of characters to find.
String1 an expression, typically a column that is searched for a specified sequence. String1 belongs to the string data category.
Start_location the character position at which to start searching for char1 in string1.
If start_location is not specified, is a negative number, or 0, the search begins at the beginning of the string1. Start_location can be of type bigint.
return character position when CHAR1 is included in string1
Returns 0 when CHAR1 is not included in string1
Ii. examples
Use AdventureWorks
SELECT CHARINDEX (' bicycle ', documentsummary)
From Production.document
WHERE DocumentID = 3;
The returned result is 48.
SELECT CHARINDEX (' Bicycle1 ', DocumentSummary, 5)
From Production.document
WHERE DocumentID = 3;
The returned result is 0.
Queries all rows that contain "bicycle" in the DocumentSummary field.
In general, we will write this:
SELECT * FROM Production.document
Where documentsummary like '%bicycle% '
Once you understand this function, you can write:
SELECT * FROM Production.document
where CHARINDEX (' bicycle ', documentsummary) >0
This method is much faster than the form of like '% '.
You can consider using SQL 2005 functions when optimizing your database.

Usage of CHARINDEX in SQL statements