CHARINDEX. Yeah. That’s right. It is one of my favorite SQL functions. I rarely see it used and most people I know don’t even know about it. Consequently, I must blab about it a little.

CHARINDEX returns the index of a successful string match.

For example:

 print charindex('cat','my cat is king')

Returns: 4

The searches can also be case sensitive:

print charindex('king','my cat is KING!' COLLATE Latin1_General_CS_AS)

returns: 0

Here is a little function I use from time to time. It grabs the text in between two characters:

Create Function [dbo].[fnBetweenCharacters](@text ntext, @startChar nchar(1), @endChar nchar(1))
RETURNS nvarchar(4000)
declare @startIndex int
declare @endIndex int
set @startIndex = charindex(@startChar, @text, 1)
set @endIndex = charindex(@endChar, @text, @startIndex+1)
return substring(@text,@startIndex+1, @endIndex - @startIndex-1)

It can be used like this:

print [dbo].[fnBetweenCharacters](N'asdkljasd@I need this text*k;lk', '@', '*')

returns: I need this text

Pass it on!

Posted by Bill Simpkins on January 14, 2009