Charindex() functionality in SQL Server

Question

Can anyone explain the actual working of CharIndex() function in SQL Server. I have gone through MSDN and other websites. But I got few doubts after reading those. I am able to understand with 2 parameters. But I am not able to understand output once we use the third parameter(Start position)Please see the below examples.

SELECT CHARINDEX('t', 'TechOnTheNet.com', 3);
-- Result: 7

SELECT CHARINDEX('t', 'TechOnTheNet.com', 8);
-- Result: 12

I am not able to understand why the results like that for the above two.

msdn.microsoft.com/en-us/library/ms186323.aspx says start_location - 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 expressionToSearch. — Pரதீப்
– Pரதீப், Commented Oct 17, 2015 at 15:32

Lukasz Szozda · Accepted Answer · 2015-10-17 15:36:41Z

2

From CHARIDNEX

start_location

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 expressionToSearch.

The searching is from ^ symbol forward.

SELECT CHARINDEX('t', 'TechOnTheNet.com', 3); -- Result: 7
                         ^---x-------->   

SELECT CHARINDEX('t', 'TechOnTheNet.com', 8); -- Result: 12
                              ^---x--->

edited Oct 17, 2015 at 15:36

answered Oct 17, 2015 at 15:30

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vamsi Prabhala · Accepted Answer · 2015-10-17 15:30:45Z

1

The third argument (optional) to charindex is the start position for the search.

SELECT CHARINDEX('t', 'TechOnTheNet.com', 3); -- Result: 7

In this case, the search for t starts at 3. But the output will be the position of t in the original string.

You can see it is similar for your other example as well.

answered Oct 17, 2015 at 15:30

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Comments

James · Accepted Answer · 2015-10-17 15:31:16Z

1

The last parameter is the start index, so that indicates the first string position where it will look for the specified character. The output is the absolute offset (one-based) of the next occurrence of the specified character.

In your example there is a T character at offsets 1, 7, and 12 (it's case-insensitive). The function returns the first occurrence greater than or equal to the specified start offset.

answered Oct 17, 2015 at 15:31

James

4,0081 gold badge30 silver badges45 bronze badges

Comments

Anthony Sherratt · Accepted Answer · 2015-10-17 15:35:39Z

1

The 3rd parameter is the starting position, so any matching patterns prior to this point are skipped.

So in your examples:-

SELECT CHARINDEX('t', 'TechOnTheNet.com', 3)

Starting position is 3, so the first 't' is skipped and returns the next one starting at position 7.

SELECT CHARINDEX('t', 'TechOnTheNet.com', 8)

Starting position is 8, so the first and second instances of 't' are skipped and returns the next one starting at position 12.

edited Oct 17, 2015 at 15:35

answered Oct 17, 2015 at 15:30

Anthony Sherratt

4755 silver badges14 bronze badges

Comments

agilgur5 · Accepted Answer · 2025-11-29 19:43:32Z

You mentioned that you have gone through the MSDN article, then I'm really clueless why you just missed the CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] ) part of that article.

It clearly said everything you want to know here:

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
Arguments

expressionToFind

A character expression containing the sequence to find. expressionToFind has an 8,000 character limit.

expressionToSearch

A character expression to search.

start_location

An integer or bigint expression at which the search starts. If start_location isn't specified, has a negative value, or has a zero (0) value, the search starts at the beginning of expressionToSearch.

Yeah. I have gone through MSDN. But I was thinking that from the start position, it is counting the number of characters till it finds the search string.

Collectives™ on Stack Overflow

Charindex() functionality in SQL Server

5 Answers 5

Comments

Comments

Comments

Comments

Arguments

expressionToFind

expressionToSearch

start_location

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Arguments

expressionToFind

expressionToSearch

start_location

2 Comments

Related