EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial Charindex SQL
 

Charindex SQL

Updated March 8, 2023

Charindex SQL

 

 

Introduction to Charindex SQL

Charindex in SQL is used to search for the position of a certain string or character in some other string. We can use this functionality to find the presence and location of the substring or certain character inside the text by simply specifying the string in which we want to search and the string that we wish to search inside that string. Further, we can optionally specify the starting location from where the search in the original string needs to begin. We have to be careful and know about the working of the CHARINDEX function. Because in case if there are multiple occurrences of the character, expression of string that we are searching for in the main string, then the function will return the position of only the first occurrence of the searched expression.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax

The syntax of the CHARINDEX function is as shown below –

CHARINDEX ( Search Expression, Original Expression [, Beginning Location ] )

Search Expression and Original Expression parameters are required parameters and need to be mentioned compulsorily, while the third parameter, Beginning Location, is optional. Search expression is the character of the string that we want to search for and whose position we wish to find out in the original string. The original string is the text, string, or character array in which we wish to search for a particular string.

Beginning location is the index from which you specify that the search for the search expression in original expression should begin with. It is optional, and hence when not specified, it has the default value of 0 as the index from where the function will begin searching. Hence, when none of the values is specified in the beginning location parameter, the search for search expression inside the original expression will begin from the start of the original string. Note that the beginning location parameter, if specified, should always be an integer value that stands for index.

Working of Charindex SQL function

CHARINDEX function internally searches for the characters that are specified in the search expression inside the original expression from the first position that is index 0 in the original string. This is done when the beginning location parameter is not specified. If an integer value is mentioned as the beginning location, then the index in the original string from where the search for the search expression will begin will be the value specified in the third parameter.

When the search for the string is being made, then as soon as the function comes across the search expression value inside the original expression, then it stops the search right there, and the index where the string is found is returned from the function. Hence, in the case of multiple occurrences of the search expression in the original expression, only the first occurrence of the searched expression is retrieved if the beginning location is not specified; else, the first occurrence of the search expression after the beginning location index is retrieved.

Examples of Charindex SQL

Let us consider one example where we will try to find the @ symbol inside some of the email addresses, say [email protected]. We have to locate the index of the @ symbol in the given mail address. For this, we will use the CHARINDEX function with search expression value as @, original string parameter with value [email protected], and none of the value as the beginning location as we want to begin the search from index 0 in the original string. Our query statement will be as follows –

SELECT CHARINDEX ('@','[email protected]') as 'AddressSignPosition';

that gives the following output after the execution of the query statement –

Charindex SQL output 1

Let us consider some other example; we have a string “Hey! Good Morning My Friend! How are you doing?”. We have to search for the second position of the exclamatory sign in the above string. For that, we would have to skip the index of the first occurrence in the search string, which is 4 in our example. Hence, we will start the search for the exclamatory sign from index 5 by specifying the beginning value parameter as 5 and the search expression as ‘!’ Sign with an original string having the value “Hey! Good Morning My Friend! How are you doing?”. Our query statement will be as follows –

SELECT CHARINDEX ('!','Hey! Good Morning My Friend! How are you doing?',5) as 'exclamatoryPosition';

that gives the following output after execution –

Charindex SQL output 2

Instead of specifying the beginning position, if we would have directly searched for the exclamatory sign in the above example, then our query statement would have been as follows –

SELECT CHARINDEX ('!','Hey! Good Morning My Friend! How are you doing?') as 'exclamatoryPosition';

and the output of the execution of the above query is as follows –

output 3

Instead of the character, now let us search ceratin substring in the main string. Consider the following example, where we will store the string to be searched in a variable and then specify it in the CHARINDEX function as shown below –

DECLARE @SearchString varchar(100)
SET @SearchString = 'Hey! Good Morning My Friend! Learn SQL with EDUCBA with Payal.'
SELECT CHARINDEX ('SQL', @SearchString) AS 'topicPosition'

The execution of the above statements gives the following output with the resultant position as 36 that is the position of SQL in the provided string.

output 4

Let us see what happens if the string or character being searched is not present in the original string. Consider the following example –

DECLARE @SearchString varchar(100)
SET @SearchString = 'Hey! Good Morning My Friend! Learn Data Science on EDUCBA with Payal.'
SELECT CHARINDEX ('SQL', @SearchString) AS 'topicPosition'

that gives the following output with 0 position, which means unavailability of the search string.

output 5

Conclusion

We can find out the presence and location of the particular character or substring inside the original string using the function CHARINDEX() in SQL. Further, we can customize the search by specifying the index from where the search for our expression should begin in the original string by specifying the beginning location, which is again an integer value that stands for index.

Recommended Articles

We hope that this EDUCBA information on “Charindex SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL Split
  2. SQL Temporary Table
  3. SQL Table Partitioning
  4. SQL NOT Operator

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW