How To Use CHARINDEX Function In SQL Server

Posted April 19, 2019 by Vishwanath Dalvi in Database, SQL Server

How To Use IF…ELSE Statement In SQL Server

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.

CHARINDEX Function In SQL Server

CHARINDEX Function In SQL Server_1

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


CHARINDEX - SQL Server

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


CHARINDEX - SQL Server

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


CHARINDEX - SQL Server

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;


CHARINDEX - SQL Server

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


CHARINDEX - SQL Server_5

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


CHARINDEX - SQL Server

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.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply