I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the CHARINDEX() function today.
The CHARINDEX() function takes a expression to find, a expression to search and a optional start position. It returns the first position in which the search string was found.
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 > length of string.
- 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 21 22 |
-- Local variables declare @var_jingle varchar(512); declare @var_find varchar(16); -- Set the strings select @var_jingle = 'silly rabbit trix are for kids'; select @var_find = 'rabbit'; -- Find location of rabbit, start at front of string select charindex(@var_find, @var_jingle) as pos1; -- Find location of rabies, start at front of string select charindex('rabies', @var_jingle) as pos2; -- Find location of rabbit, start at end of string select charindex(@var_find, @var_jingle, 31) as pos3; -- Null search string select charindex(NULL, @var_jingle) as pos4; -- Null source string select charindex(@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.
- 0 – Expression to find not found.
- NULL – Position is unknown.
- NULL – Position is unknown.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
output: pos1 ----------- 7 pos2 ----------- 0 pos3 ----------- 0 pos4 ----------- NULL pos5 ----------- NULL |
Next time, I will be exploring the CONCAT() function. This new function was introduction in SQL Server 2012.
