EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Charindex SQL

Home » Data Science » Data Science Tutorials » SQL Tutorial » Charindex SQL

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.

Syntax

The syntax of the CHARINDEX function is as shown below –

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

Popular Course in this category
Sale
JDBC Training (6 Courses, 7+ Projects)6 Online Courses | 7 Hands-on Projects | 37+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (9,284 ratings)
Course Price

View Course

Related Courses
PHP Training (5 Courses, 3 Project)Windows 10 Training (4 Courses, 4+ Projects)SQL Training Program (7 Courses, 8+ Projects)PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

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 payaludhani26@gmail.com. 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 payaludhani26@gmail.com, 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 ('@','payaludhani26@gmail.com') 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

This is a guide to Charindex SQL. Here we discuss the Working of Charindex SQL and the Examples along with the statements and outputs. You may also have a look at the following articles to learn more –

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

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
SQL Tutorial
  • Advanced
    • SQL Formatter
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • SQL REVOKE
    • SQL Select Distinct Count
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL BIGINT
    • SQL Crosstab
    • SQL Wildcard Character
    • SQLAlchemy Filter
    • SQLAlchemy SQLite
    • SQLAlchemy DateTime
    • SQLAlchemy create_engine
    • SQL INSTR()
    • SQL now
    • SQL synonyms
    • SQLite?export to csv
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • ROLLUP in SQL
    • Escape Character SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
    • SQL if then else
    • SQL ignore-case
    • SQL Matches
    • SQL Search String
    • SQL Column Alias
    • SQL extensions
    • SQL Substring Function
    • Charindex SQL
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • SQL Server Versions
    • SQL Case Insensitive
    • SQL Expressions
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL WAITFOR
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • SQL GROUP BY WHERE
    • SQL ROW
    • SQL EXECUTE
    • SQL EXCLUDE
    • SQL Performance Tuning
    • SQL UUID
    • Begin SQL
    • SQL Update Join
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • sqlplus set commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • GROUP BY clause in SQL
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DESC
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • Alternate Key in SQL
    • SQL Super Key
  • Functions
    • SQL Date Function
    • SQL String Functions
    • SQL Compare String
    • Timestamp to Date in SQL
    • SQL Window Functions
    • SQL CONCAT
    • SQL ALTER TABLE
    • SQL MOD()
    • SQL Timestamp
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • SQL HOUR()
    • SQLite?functions
    • ANY in SQL
    • LIKE Query in SQL
    • SQL NOT NULL
    • SQL NOT IN
    • SQL MAX()
    • SQL MIN()
    • SQL SUM()
    • SQL COUNT
    • SQL identity
    • SQL DELETE Trigger
    • SQL Declare Variable
    • SQL Text Search
    • SQL COUNT DISTINCT
    • SQL TEXT
    • SQL Limit Order By
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL MOD()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Server Permission
    • T-SQL INSERT
    • T-SQL Stuff
    • T-SQL ADD Column
    • SQL Ranking Function
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • LEFT OUTER JOIN in SQL
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • NoSQ
    • NoSQL Databases List
    • NoSQL Injection
    • NoSQL vs SQL Databases
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

4th of July Offer - JDBC Training Course Learn More