CHARINDEX() in SQL Server

CHARINDEX() function returns the position of the string to search in the string to be searched.

Syntax: CHARINDEX ( search string, string to be searched, position search to start )

Remarks:

    1. CHARINDEX is a case insensitive search

    2. CHARINDEX always returns first occurrence of the search word from the search position is defined

    3. If search position is not defined, it returns the first occurrence position

Examples:

Declare @SearchtoString varchar(100) = 'SQL Server is a database server. I love SQL Server.'
Declare @SearchString varchar(100) = 'Server'

SELECT CHARINDEX(@SearchString, @SearchtoString,4) AS MatchPosition;


--Another example
;With cte as
(
	Select @SearchtoString stringtobesearched, CHARINDEX(@SearchString, @SearchtoString, 0) StringPosition
	union all 
	Select stringtobesearched, CHARINDEX(@SearchString, stringtobesearched, StringPosition+1) From cte
	where StringPosition > 0
)
Select * From cte where StringPosition  0

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

One thought on “CHARINDEX() in SQL Server”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s