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;

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

Read Also:  how to insert multiple json data into mysql database in php? - Best 3 Examples

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.

Read Also:  php remove comma from string -how to remove comma from string in php?

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.