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.
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 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., which means that the word
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.
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.
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.
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.
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
WITH TAG AS (SELECT 'PHP | JAVA | PYTHON' AS Category) SELECT CHARINDEX('|', Category, CHARINDEX('|', 'PHP | JAVA | PYTHON') + 1) Location FROM TAG
The result is.
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.
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.