« December 2008 | Main | March 2009 »
January 14, 2009
CHARINDEX ?
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)
AS
BEGIN
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)
END
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 at 5:32 PM | Comments (1) | TrackBack