« Profiling Lock Contention | Main | Subtle Differences »
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 January 14, 2009 5:32 PM
Trackback Pings
TrackBack URL for this entry:
http://blog.logos.com/mt-cgi/mt-tb.cgi/277
Comments
Hey guys,
I really like the content you posted to this blog last year and have discovered several real gems of code though your posts.
I've noticed you haven't been posting much this year so I thought I'd flick you a message to say that at least one person would like to hear more from you!
- Josh.
Posted by: Josh at March 4, 2009 4:16 PM