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
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.
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.