mysql substring in PHP MySQL – MySQL SUBSTRING() Function

mysql substring – MySQL SUBSTR() returns the specified number of characters from a particular position of a given string.

mysql substring || SUBSTRING() function in MySQL

MySQL Substring And Substring_Index Functions With MySQL SUBSTRING Example, Without Count, With Count, With Negative Index Values, Index Out of Bounds, MySQL SUBSTRING_INDEX, Searching For A Character, Searching For A String, Searching For A Non-Existing String, Searching For Character Or String In Reverse Order and Applications Of MySQL SUBSTRING Function.

mysql substring Syntax :

SUBSTRING(string, start, length)
OR
SUBSTRING(string FROM start FOR length)

Example 1

start at position 5, extract 3 characters

SELECT SUBSTRING("PAKAINFO Article", 5, 3) AS final_results;

Example 2

start at position 2, extract 5 characters

SELECT SUBSTRING(WebsiteName, 2, 5) AS final_results
FROM Websites;

Free Live Chat for Any Issue

Example 3

start from the end, at position -5, extract 5 characters

SELECT SUBSTRING("PAKAINFO Article", -5, 5) AS final_results;

don’t Miss : PHP MySQL LIKE Operator String Function

Also Read This ๐Ÿ‘‰   Laravel Eloquent JOIN Multiple Tables using Query Builder

With Count

SELECT SUBSTRING('Free Donwload Tamilrokers Movies', 10, 7) as final_results;
 
SELECT SUBSTRING('Free Donwload Tamilrokers Movies' FROM 10 FOR 7) as final_results;

With Negative Index Values

SELECT SUBSTRING('Free Donwload Tamilrokers Movies', -12, 7) as final_results;
 
SELECT SUBSTRING('Free Donwload Tamilrokers Movies' FROM -12 FOR 7) as final_results;

Index Out of Bounds

SELECT SUBSTRING('Free Donwload Tamilrokers Movies', 30, 2) as final_results;

SELECT SUBSTRING('Free Donwload Tamilrokers Movies', 5, 100) as final_results;

SELECT SUBSTRING('Free Donwload Tamilrokers Movies', 5, -20) as final_results;

Searching For A Character

SELECT SUBSTRING_INDEX('Free Donwload Tamilrokers Movies', 'T', 1) as final_results;

SELECT SUBSTRING_INDEX('Free Donwload Tamilrokers Movies', 'e', 1) as final_results;

SELECT SUBSTRING_INDEX('Free Donwload Tamilrokers Movies', 'e', 2) as final_results;

SELECT SUBSTRING_INDEX('Free Donwload Tamilrokers Movies', 'e', 2) as final_results;

SELECT SUBSTRING_INDEX('Free Donwload Tamilrokers Movies', 't', 1) as final_results;

Searching For A String

SELECT SUBSTRING_INDEX('Free Donwload Tamilrokers Movies', 'Movies', 1) as final_results;

Searching For A Non-Existing String

SELECT SUBSTRING_INDEX('Free Donwload Tamilrokers Movies', 'abc', 1) as final_results;

Searching For Character Or String In Reverse Order

SELECT SUBSTRING_INDEX('Free Donwload Tamilrokers Movies', 'T', -1) as final_results;

Applications Of MySQL SUBSTRING Function

Example: Assume that we have an member infromation table consisting of id, name, and location.

Also Read This ๐Ÿ‘‰   what does pairwise distinct mean?

Below is the Script to create a table and insert dummy data:

CREATE TABLE members_info (name VARCHAR(100), location VARCHAR(100), age INT);
 
INSERT INTO members_info values
('Rajiv Gandhi', '102,Ganesh new', 25), 
('Virat Kohali','88/3,Gokukl park',35),
('Sachin Tendulkar','190,Tirupati sco',40);

SELECT name, SUBSTRING_INDEX(location,',',1) as scoget, location, age from members_info

I hope you get an idea about mysql substring.
I would like to have feedback on my infinityknow.com.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, donโ€™t forget to share.