SQL Server CHARINDEX Function with Examples

The primary use of an SQL Server CHARINDEX function is to find the first or starting location of an expression or characters in a given string. To make it simple, it is like searching a specified character or characters in a string.

sql server charindex function

Syntax

CHARINDEX (expression_to_find, expression_to_search, start_location)

The standard CHARINDEX function takes three parameters. The third or the final parameter, which is an Integer value, is optional. Let us understand all the parameters in detail.

1) expression_to_find

The first parameter is a character or a string of characters that we want to search in another string.

Let us assume we have a string Light Years Away and we want to get the location of the word Years in the string. So the first parameter will be the word Years in the function.

SELECT CHARINDEX('YEARS', 'Light Years Away') Location

The result of this query is 7, which means that the word Years starts at location seven in the above string. A sentence like this can have multiple occurance of a particular word or character, but the function will find the first location of the expression. The later occurances will be ignored.

2) expression_to_search

An expression can be a phrase or a sentence with characters like alphabet, numbers and other special characters. The second parameter in the function CHARINDEX is typically a sentence with many characters. This parameters can also be either a variable or a column name.

start_location

The third parameter is optional (you may or may not use it) and it is an Integer value. In case you wish to locate or find an expression (expression_to_find) starting from a particular location in a string (expression_to_search), then you have to put the figure. If you do not mention the third parameter in the function, then the search starts from the position 0.

Related: SQL Server SUBSTRING Function with Examples

More examples

Here in our example, we have a list of categories separated by the symbol pipe |.

PHP | JAVA | PYTHON

1) The query to find the first position of the symbol “|” will be as follows.

SELECT CHARINDEX('|', 'PHP | JAVA | PYTHON') Location

The result is 5.

2) Use of start_location

There are three categories separated by two pipes and we now need to find the position of the second pipe in the string. To do this we have to mention a start_location after the first pipe, since the default search will start from 0 and the result will end up showing location 5.

SELECT CHARINDEX('|', 'PHP | JAVA | PYTHON', 6) Location

The result is now 12.

3) Use of multiple CHARINDEX function in a single query.

Continuing with example 2, let us assume we do not have the start_location before hand an we have to find it dynamically.

SELECT CHARINDEX('|', 'PHP | JAVA | PYTHON', CHARINDEX('|', 'PHP | JAVA | PYTHON') + 1) Location

Or

WITH TAG AS (SELECT 'PHP | JAVA | PYTHON' AS Category)
SELECT CHARINDEX('|', Category, CHARINDEX('|', 'PHP | JAVA | PYTHON') + 1) Location
FROM TAG

The result is 12.

4) Ok, we found locations of the pipe. Now, using the locations we need to extract a value from the above-mentioned string.

WITH TAG AS (SELECT 'PHP | JAVA | PYTHON' AS Category)
SELECT SUBSTRING(Category, 1, CHARINDEX('|', Category) - 1) Category
FROM TAG

The result is PHP.

In the above example, we are using another SQL function called the SUBSTRING in accord with CHARINDEX to get a value from the searched string.

Previous - SQL Server – Show Date and Time as Blank instead of “1900-01-01”Next - SQL Server SUBSTRING Function with Examples



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Google+

Related Posts: