I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the PATINDEX() function today.
The PATINDEX() function takes a search pattern to find and a expression to search as input parameters. It returns the first position in which the search pattern was found. Unlike the CHARINDEX() function, wild card characters such as %, _, and ^ can be used used to define the pattern. In a nutshell, it is a fuzzy match that is performed.
The following example is based upon the television jingle that I heard as a kid for Trix cereal.
The example below contains four test cases:
- Word is found in target string.
- Word is not found in target string.
- Starting position any word that starts with tr.
- NULL expression to find.
- NULL expression to search.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- Set local variables declare @var_jingle varchar(512) = 'silly rabbit trix are for kids'; declare @var_find varchar(16) = '%rabbit%'; -- Find location of rabbit (7) select patindex(@var_find, @var_jingle) as pos1; -- Find location of rabies (0) select patindex('%rabies%', @var_jingle) as pos2; -- Find location of any word starting with tr, 14 select patindex('%tr__%', @var_jingle) as pos3; -- Null search string, NULL select patindex(NULL, @var_jingle) as pos4; -- Null source string, error select patindex(@var_find, NULL) as pos5; |
The results of four test cases are the following:
- 7 – Seventh character position is start of string.
- 0 – Expression to find not found.
- 14 – Expression is matched to trix.
- NULL – Position is unknown.
- ERROR – This function call generates an error.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
output: pos1 ----------- 7 pos2 ----------- 0 pos3 ----------- 14 pos4 ----------- NULL Msg 8116, Level 16, State 1, Line 20 Argument data type NULL is invalid for argument 2 of patindex function. |
Next time, I will be exploring the QUOTENAME() function that comes in handy when creating dynamic TSQL with object names that might need to be quoted.
