The PATINDEX() function is to find the position number that matches a certain expression/pattern in a string value.
Syntax:
PATINDEX ( '%pattern%' , input_string )
In this syntax:
- pattern is the character expression/pattern being searched for. This part can contain wildcard characters such as % and '_'. Wildcards here are the same as that used in the LIKE operator.
- input_string adalah teks string yang akan dicari karakter/pola yang sesuai di dalamnya.
The PATINDEX function will return the position number value according to the pattern. And if there are several matches then the position value of the first number will be returned.
The PATINDEX function will return 0 if no matching character/pattern is found. And will return a NULL value if the pattern or input_string is NULL.
e.g. PATINDEX function with % wildcard
SELECT PATINDEX('%ern%', 'Ayo Belajar Menggunakan SQL Pattern Index') AS position
e.g. PATINDEX function with several wildcards ( % and _ ).
SELECT PATINDEX('%S_L%', 'Ayo Belajar Menggunakan SQL Pattern Index') AS position
e.g. PATINDEX function using database table.
SELECT [name], PATINDEX('%PARCEL%', [name]) AS position
FROM [dbo].[item]
WHERE PATINDEX('%PARCEL%', [name]) > 0
0 Comments