Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » String FunctionsRSS Feeds

CHARINDEX Function

Posted By: Sarita Patel     Category: SQL Server     Views: 43097

This article explains CHARINDEX function of sql server with examples.

CHARINDEX is used to get starting position of the specified expression in a string. 


Syntax of CHARINDEX Function :

CHARINDEX (expression1 ,expression2 [ ,start_location ] )

expression1 is a sequence of characters to be found from expression2. It is of short character data type.

expression2 is a string from which expression1 is searched. It is of character string data type. 

start_location is a position to start searching for expression1 in expression2. It is an optional field. If you don't specify or specify 0 or negative number the search starts from beginning of expression2

It returns an integer value.


Examples of CHARINDEX Function :

Example 1 : Use of CHARINDEX function in select clause

SELECT CHARINDEX('ax','Syntax-Example-Syntax',0) 
OR
SELECT CHARINDEX('ax','Syntax-Example-Syntax') 

Output
5

Above example returns position of starting index for characters 'ax' in specified string. It starts searching from beginning of the string. As it starts searching from beginning of the string, it returns position of 1st occurrence of 'ax' from expression2.


SELECT CHARINDEX('ax','Syntax-Example-Syntax',6) 
Output
20

Above example returns position of starting index for characters 'ax' in specified string, this time it starts searching after starting 6 characters. As it starts searching after 6 characters, it returns position of 2nd occurrence of 'ax' from expression2.


Example 2 : Use of CHARINDEX function to display field value of table

SELECT ContactName, CHARINDEX('an',ContactName) AS 'Index'
FROM   Customers

Output
ContactName             Index
Maria Anders              7
Ana Trujillo                 1
Antonio Moreno          1
Thomas Hardy            0
Christina Berglund       0
Hanna Moos               2

Above example displays starting position of 'an' in all customer names from customers table.



Example 3 : Use of CHARINDEX function in where clause

SELECT ContactName
FROM    Customers
WHERE  CHARINDEX('an',ContactName) > 0

Output
ContactName
Maria Anders
Ana Trujillo
Antonio Moreno
Hanna Moos

Above example displays all customer names having substring 'an' in customer name of customers table.


  
Share: 

 
 
 

Didn't find what you were looking for? Find more on CHARINDEX Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of CHARINDEX Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!