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 SELECT CHARINDEX('"',@a) -- Find the Second " in the string by starting one character past the first one SELECT CHARINDEX('"',@a,CHARINDEX('"',@a)+1) -- Put it together SELECT SUBSTRING(@a, -- Start from first " CHARINDEX('"',@a), -- 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 SELECT SUBSTRING(@a, -- Add 1 to trim off beginning quote CHARINDEX('"',@a)+1, -- 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. SELECT CASE WHEN CHARINDEX('"',@a,CHARINDEX('"',@a)+1)-1 - CHARINDEX('"',@a)) > 0 THEN SUBSTRING(@a, CHARINDEX('"',@a)+1, CHARINDEX('"',@a,CHARINDEX('"',@a)+1)-1 - CHARINDEX('"',@a)) ELSE 'Not Found' End