- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
How is it possible in MySQL to find the location of the first occurrence of a substring in a string?
MySQL LOCATE() function makes it possible to find the location of the first occurrence of a substring in a string. Following is the syntax of using it −
In this function, Substring is the string whose position of occurrence needs to find and the string is a string from which the occurrence of substring needs to be searched.
We must have to pass both the strings (i.e. substring, which is to be searched and the string, from which substring is to be searched) as arguments of the LOCATE() function.
mysql> Select LOCATE('Good','RAM IS A GOOD BOY')As Result; +--------+ | Result | +--------+ | 10 | +--------+ 1 row in set (0.00 sec)
In the above example, the output is 10 because the first occurrence of substring ’good’ start from 10th position in the string ‘RAM IS A GOOD BOY’.
However, we can also manage the starting point of searching with the help of another argument representing position. The syntax of LOCATE() with position argument would be as follows −
LOCATE(Substring, String, position)
mysql> Select LOCATE('Good','Good, RAM IS A GOOD BOY')As Result; +--------+ | Result | +--------+ | 1 | +--------+ 1 row in set (0.00 sec) mysql> Select LOCATE('Good','Good, RAM IS A GOOD BOY',5)As Result; +--------+ | Result | +--------+ | 16 | +--------+ 1 row in set (0.00 sec)
From the above result set, we can see the difference between using position argument. If we do not use it the search by default starts from the 1st position and when we specify ‘5’ as the position argument in our example it starts from 5th position.
- Related Articles
- Get the first occurrence of a substring within a string in Arduino
- How to find the nth occurrence of substring in a string in Python?
- How to find index of last occurrence of a substring in a string in Python?
- Get the substring after the first occurrence of a separator in Java
- How it is possible in MySQL to find a string of specified pattern within another string?
- Get the last occurrence of a substring within a string in Arduino
- Is it possible to predict the occurrence of an earthquake?
- How can it be possible to invert a string in MySQL?
- How to find the location of a string in an R data frame?
- How can we replace all the occurrences of a substring with another substring within a string in MySQL?
- Get the substring before the last occurrence of a separator in Java
- How to find the first character of a string in C#?
- Get the index of last substring in a given string in MySQL?