- MySQL Basics
- MySQL - Home
- MySQL - Introduction
- MySQL - Features
- MySQL - Versions
- MySQL - Variables
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Node.js Syntax
- MySQL - Java Syntax
- MySQL - Python Syntax
- MySQL - Connection
- MySQL - Workbench
- MySQL Databases
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Show Database
- MySQL - Copy Database
- MySQL - Database Export
- MySQL - Database Import
- MySQL - Database Info
- MySQL Users
- MySQL - Create Users
- MySQL - Drop Users
- MySQL - Show Users
- MySQL - Change Password
- MySQL - Grant Privileges
- MySQL - Show Privileges
- MySQL - Revoke Privileges
- MySQL - Lock User Account
- MySQL - Unlock User Account
- MySQL Tables
- MySQL - Create Tables
- MySQL - Show Tables
- MySQL - Alter Tables
- MySQL - Rename Tables
- MySQL - Clone Tables
- MySQL - Truncate Tables
- MySQL - Temporary Tables
- MySQL - Repair Tables
- MySQL - Describe Tables
- MySQL - Add/Delete Columns
- MySQL - Show Columns
- MySQL - Rename Columns
- MySQL - Table Locking
- MySQL - Drop Tables
- MySQL - Derived Tables
- MySQL Queries
- MySQL - Queries
- MySQL - Constraints
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Replace Query
- MySQL - Insert Ignore
- MySQL - Insert on Duplicate Key Update
- MySQL - Insert Into Select
- MySQL Indexes
- MySQL - Indexes
- MySQL - Create Index
- MySQL - Drop Index
- MySQL - Show Indexes
- MySQL - Unique Index
- MySQL - Clustered Index
- MySQL - Non-Clustered Index
- MySQL Operators and Clauses
- MySQL - Where Clause
- MySQL - Limit Clause
- MySQL - Distinct Clause
- MySQL - Order By Clause
- MySQL - Group By Clause
- MySQL - Having Clause
- MySQL - AND Operator
- MySQL - OR Operator
- MySQL - Like Operator
- MySQL - IN Operator
- MySQL - ANY Operator
- MySQL - EXISTS Operator
- MySQL - NOT Operator
- MySQL - NOT EQUAL Operator
- MySQL - IS NULL Operator
- MySQL - IS NOT NULL Operator
- MySQL - Between Operator
- MySQL - UNION Operator
- MySQL - UNION vs UNION ALL
- MySQL - MINUS Operator
- MySQL - INTERSECT Operator
- MySQL - INTERVAL Operator
- MySQL Joins
- MySQL - Using Joins
- MySQL - Inner Join
- MySQL - Left Join
- MySQL - Right Join
- MySQL - Cross Join
- MySQL - Full Join
- MySQL - Self Join
- MySQL - Delete Join
- MySQL - Update Join
- MySQL - Union vs Join
- MySQL Keys
- MySQL - Unique Key
- MySQL - Primary Key
- MySQL - Foreign Key
- MySQL - Composite Key
- MySQL - Alternate Key
- MySQL Triggers
- MySQL - Triggers
- MySQL - Create Trigger
- MySQL - Show Trigger
- MySQL - Drop Trigger
- MySQL - Before Insert Trigger
- MySQL - After Insert Trigger
- MySQL - Before Update Trigger
- MySQL - After Update Trigger
- MySQL - Before Delete Trigger
- MySQL - After Delete Trigger
- MySQL Data Types
- MySQL - Data Types
- MySQL - VARCHAR
- MySQL - BOOLEAN
- MySQL - ENUM
- MySQL - DECIMAL
- MySQL - INT
- MySQL - FLOAT
- MySQL - BIT
- MySQL - TINYINT
- MySQL - BLOB
- MySQL - SET
- MySQL Regular Expressions
- MySQL - Regular Expressions
- MySQL - RLIKE Operator
- MySQL - NOT LIKE Operator
- MySQL - NOT REGEXP Operator
- MySQL - regexp_instr() Function
- MySQL - regexp_like() Function
- MySQL - regexp_replace() Function
- MySQL - regexp_substr() Function
- MySQL Fulltext Search
- MySQL - Fulltext Search
- MySQL - Natural Language Fulltext Search
- MySQL - Boolean Fulltext Search
- MySQL - Query Expansion Fulltext Search
- MySQL - ngram Fulltext Parser
- MySQL Functions & Operators
- MySQL - Date and Time Functions
- MySQL - Arithmetic Operators
- MySQL - Numeric Functions
- MySQL - String Functions
- MySQL - Aggregate Functions
- MySQL Misc Concepts
- MySQL - NULL Values
- MySQL - Transactions
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - SubQuery
- MySQL - Comments
- MySQL - Check Constraints
- MySQL - Storage Engines
- MySQL - Export Table into CSV File
- MySQL - Import CSV File into Database
- MySQL - UUID
- MySQL - Common Table Expressions
- MySQL - On Delete Cascade
- MySQL - Upsert
- MySQL - Horizontal Partitioning
- MySQL - Vertical Partitioning
- MySQL - Cursor
- MySQL - Stored Functions
- MySQL - Signal
- MySQL - Resignal
- MySQL - Character Set
- MySQL - Collation
- MySQL - Wildcards
- MySQL - Alias
- MySQL - ROLLUP
- MySQL - Today Date
- MySQL - Literals
- MySQL - Stored Procedure
- MySQL - Explain
- MySQL - JSON
- MySQL - Standard Deviation
- MySQL - Find Duplicate Records
- MySQL - Delete Duplicate Records
- MySQL - Select Random Records
- MySQL - Show Processlist
- MySQL - Change Column Type
- MySQL - Reset Auto-Increment
- MySQL - Coalesce() Function
MySQL LOCATE() Function
The MySQL LOCATE() function is used to find the position of a substring within a given string.
It takes a string value and a substring as parameters and returns the position of the first occurrence of the specified substring in the original string. If the substring is not found within the original string, it returns 0 as the output.
It is important to note that this function performs a case-insensitive search.
Syntax
Following is the syntax of MySQL LOCATE() function −
LOCATE(substr, str);
Parameters
This function takes a substring and a string to search as a parameter.
Return Value
This function returns the position of the first occurrence of the substring within the string or 0 if not found.
Another Syntax
The function also has a second syntax where, in addition to the string values, it accepts another numerical value as a parameter, known as pos. It returns the position of the first occurrence of the specified substring in the original string, starting from the position pos −
LOCATE(substr,str,pos);
Example
In the following example, we are using the LOCATE() function to find the position of the substring 'how' within the given string −
SELECT LOCATE('how', 'Hello how are you');
Following is the output of the above code −
LOCATE('how', 'Hello how are you') |
---|
7 |
Example
Following example demonstrates the usage of the second syntax of the LOCATE() function. Here, we specify the pos value as 5, and it returns the position of the substring 'point' starting from the 5th position −
SELECT LOCATE('point', 'Tutorialspoint', 5);
Output of the above code is as shown below −
LOCATE('point', 'Tutorialspoint', 5) |
---|
10 |
Example
You can also pass numerical values as arguments to the first parameter in the form of a string −
SELECT LOCATE('112', '6522 7262 8972 92 112', 5);
The output obtained is as follows −
LOCATE('112', '6522 7262 8972 92 112', 5) |
---|
19 |
Example
If the substring provided is not found within the given string, the function returns 0 −
SELECT LOCATE('unknown', 'Tutorialspoint');
The result produced is as follows −
LOCATE('unknown', 'Tutorialspoint') |
---|
0 |
Example
If any of the arguments passed to this function is NULL, it returns NULL −
SELECT LOCATE(NULL, 'Tutorialspoint');
Following is the output of the above code −
LOCATE(NULL, 'Tutorialspoint') |
---|
NULL |
Example
Let us create a table named "EMP" and insert records into it using CREATE and INSERT statements as shown below −
CREATE TABLE EMP( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, INCOME FLOAT );
Now, let us insert records into it using the INSERT statement −
INSERT INTO EMP VALUES ('Krishna', 'Sharma', 19, 2000), ('Raj', 'Kandukuri', 20, 7000), ('Ramya', 'Ramapriya', 25, 5000), ('Mac', 'Mohan', 26, 2000);
The EMP obtained is as follows −
FIRST_NAME | LAST_NAME | AGE | INCOME |
---|---|---|---|
Krishna | Sharma | 19 | 2000 |
Raj | Kandukuri | 20 | 7000 |
Ramya | Ramapriya | 25 | 5000 |
Mac | Mohan | 26 | 2000 |
Following query searches for 'ma' in the 'LAST_NAME' column of the EMP table −
SELECT FIRST_NAME, LAST_NAME, AGE, LOCATE('ma', LAST_NAME) as result FROM EMP;
After executing the above code, we get the following output −
FIRST_NAME | LAST_NAME | AGE | result |
---|---|---|---|
Krishna | Sharma | 19 | 5 |
Raj | Kandukuri | 20 | 0 |
Ramya | Ramapriya | 25 | 3 |
Mac | Mohan | 26 | 0 |
To Continue Learning Please Login
Login with Google