SQL Server has added support for various string functions. Coupled with manipulation and pattern finding features. String function takes a string as input and returns numeric value or another string as an output. Depends on the type of function used. Usually while working with string, we need to find the first occurrence of a character or string. SQL Server added CHARINDEX function to help with this.
CHARINDEX – a string function used to find the start index of the first occurrence of input string inside another target string. It returns the location, index number of the first occurrence. CHARINDEX provides a similar output as IndexOf function in C#. The function accepts three parameters whereas the third parameter is optional.
Syntax & Parameters
CHARINDEX ( input_string, target_string, [start_location])
Input String – String to be found (up to 8000 chars)
Target String – To search within a string (valid string data type)
Start location – Optional parameter, to begin the search at a specified index location. If not specified, or specified as 0 or negative number then the search starts at the beginning in the target string.
CHARINDEX – Points to Ponder
1.Returns a number, index location of a substring within a string.
2.Return value is of BIGINT data type – if target string is varchar(max), nvarchar(max) else INT data type.
3.Search is NOT case sensitive. Thus lowercase c will match uppercase C as well as lowercase c in search.
4.If input substring is not found then CHARINDEX returns 0.
5.If any of the input string is NULL then CHARINDEX returns NULL as output.
CHARINDEX – Examples
Let’s walk through with examples to understand CHARINDEX in detail.
1.
CHARINDEX – Return Starting Position In Email Id
In the above example, CHARINDEX function finds the index location of @ character within email id and returns the number. If @ is repeated twice, the function will return the index of the first occurrence of @.
Use Tempdb GO SELECT CHARINDEX('@', '[email protected]') as Test1; -- 17 SELECT CHARINDEX('@', '[email protected]') as Test2; -- 7 SELECT CHARINDEX('@', '[email protected]') as Test3; -- 6
2.
CHARINDEX – Return Starting Position of Substring Within String
In this example, finding a substring within a string. Pay attention to the second query where the substring is repeated twice. However, CHARINDEX returns the index of the first occurrence of The as 1.
Use Tempdb GO SELECT CHARINDEX('Recipes', 'Tech-Recipes') as Test1; -- 6 SELECT CHARINDEX('The', 'The Sun The') as Test2; -- 1 SELECT CHARINDEX('Poetry', 'Hello Poetry - Words Magic') as Test3; -- 7
3.
CHARINDEX – Using Third Parameter – Start Location
Below example uses the third parameter in CHARINDEX. Start location specifies that search will begin from a specified index location. Referring to the first query, the substring is repeated twice but function starts the search at index 5, thus returning output as 9. Finding the second occurrence of The.
Use Tempdb GO SELECT CHARINDEX('The', 'The Sun The', 5) as Test1; -- 9 SELECT CHARINDEX('Hello', 'Hello Poetry - Hello Dear', 7) as Test2; -- 16
4.
CHARINDEX – With CASE Statement – Check If SubString Exists
Using CHARINDEX with CASE statement. We can verify if a string or a character exists in another string. If CHARINDEX function returns an index number which is NON-ZERO then the character or word exists. Otherwise, it doesn’t exist.
DECLARE @Name as VARCHAR(100) = 'Sha Ka Ba'; SELECT CASE WHEN CHARINDEX('Ka', @Name) > 0 THEN 'Exists' ELSE 'Not Exists' END as Test; SELECT CASE WHEN CHARINDEX('Pk', @Name) > 0 THEN 'Exists' ELSE 'Not Exists' END as Test;
5.
CHARINDEX – Substring Not Found
Following examples demonstrates that CHARINDEX function returns 0 if a substring is not found.
Use tempdb; Go SELECT CHARINDEX('Hi', 'Hey Hello There') as Test1; --Not Found SELECT CHARINDEX('Okay', 'Alright - Fine') as Test2; --Not Found
6.
CHARINDEX – CASE Sensitive Search
Previous examples didn’t force any case sensitive search. Thus searching for substring the would match up with THE (uppercase) and the (lowercase). Our examples were executed under a database with collation as case insensitive.
In the following example, we are trying to match MANGO (case sensitive).
--Forcing Case Sensitive Search --Matches SELECT CHARINDEX('MANGO', 'MANGO is king of fruit' COLLATE Latin1_General_CS_AS) as Test1; --Ignores in Case Sensitive Search SELECT CHARINDEX('Mango', 'MANGO is king of fruit' COLLATE Latin1_General_CS_AS) as Test2; --Default Collation is Case Insensitive SELECT CHARINDEX('Mango', 'MANGO is king of fruit') as Test3; --Matches
Summary
In summary, we have learnt the use of CHARINDEX function in SQL Server. Using this function we can find the first occurrence of a substring within a string. If you like this post, you may read through Tech-Recipes database archives for more useful stuff.
