SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

String Manipulation - CHARINDEX()

I see charindex used quite commonly in string manipulation.  What I rarely see used is the optional third parameter.  Here I will explain how to use it, a situation where it can be useful and something to be careful of.


CHARINDEX ( expression1 ,expression2 [ , start_location ] )

Is a character expression that contains the sequence to be found. expression1 is limited to 8000 characters.

Is a character expression to be searched.

Is an integer or bigint expression at which the search starts. If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expression2.

How it works
Let's say we have a string with a set of characters in it and you want to find  what is in between them.  Easy enough if they're different characters like [] or () (You could still use this for that situation to ensure you didn't get an ending char before your starting char, and the warning below is still valid).  But what if they're the same character such as "" or --?  Here's how to use the third parameter of charindex along with a substring to accomplish this.

-- Create a sample string with a pair of quotes.  The goal is to get what is in between the quotes. 
declare @a varchar(50)
SET @a = 'This is a "test" string.' 

-- Find the First " in the string 
-- Find the Second " in the string by starting one character past the first one 
-- Put it together 
                -- Start from first " 
                -- Position of the second " minus the position of the first " to find length. 
                CHARINDEX('"',@a,CHARINDEX('"',@a)+1) - CHARINDEX('"',@a))

We're almost there.  If you run the code, you'll notice that the result is "test .  Let's trim off that extra ".

-- Clean it up 
                -- Add 1 to trim off beginning quote 
                -- Subtract 1 to trim off ending quote                            
                CHARINDEX('"',@a,CHARINDEX('"',@a)+1)-1 - CHARINDEX('"',@a))


This is really a caution of the substring function, not charindex, but it is something that will cause the above technique to fail (and error).  The problem occurs when you do not find the characters that you're looking for.  In the above example, I was looking for two "'s.  If they don't exist, the code will error out.  If no quotes are present, it works up until the cleanup portion.  If only one " is present, it fails at the first substring.  You will receive this error:

Msg 537, Level 16, State 2, Line 20

Invalid length parameter passed to the LEFT or SUBSTRING function.

The reason for this is that it attempts to pass a negative length to the substring function, which isn't allowed.  To safeguard against this, you can use a case statement like the following:

DECLARE @a varchar(50)
SET @a = 'This is a "fail  string.' 

                -- Test for a second " before attempting the substring. 
                CHARINDEX('"',@a,CHARINDEX('"',@a)+1)-1 - CHARINDEX('"',@a))
            ELSE 'Not Found'


Posted by Steve Jones on 19 December 2009

Good info, and I've often used the 3rd parameter when looping through strings, doing some sort of string separation routines. It's very handy, one of my most-often-used functions.

Posted by Vijaya Kadiyala on 21 December 2009

The 3rd parameter is very handy in string split operations..

Thanks -- Vj


Posted by bhavesh on 18 January 2010

In CHARINDEX, can expression1 be a variable?

I have list of SPs in temp table,

I am trying to get SP's start position

select @findspstart = charindex(@spname,spdefinition,@create)  from #abc

Its returning 0

Thanks in advance.

Posted by TRACEY on 24 April 2011

If use this

'[Error]CA (open)   Invalid Forecast    [Error]WP (open) / 10000 / Rev 00  Invalid '

Can't get the 2nd @a to be [Error]WP  starting position of the 2nd [

Leave a Comment

Please register or log in to leave a comment.