MySQL SubString

A substring is a very important function in SQL. Substring is defined as a part of a bigger string.  MySQL Substring function is, thus, used to extract a section of an existing string.

For example you may want to take first three characters of all city names to get the corresponding short city names. In another case you may want to join the first character of first names of employees with their last names to create organizational email IDs for them. For such tasks that may be needed to be done on a huge set of values MySQL Substring function will be a great help.  The string can be a constant string value or values stored in the fields of a table.

Syntax MySQL SubString

Substring function in MySQL can be used in two forms. One format requires only position in string starting from where the substring will be taken to the last character of the parent string. The second format requires the start position and length of the substring from that point.

With Start Position only

SUBSTRING(source_string, start_position)

To get a section of a string you need to give the source string or a data field name. Second argument of his function is “from” position from where you want to start extracting the section. A negative position will be taken from backwards of the string. If position is positive, a string is returned till the last character of the source string. If position is negative, a string is returned till the first character of the source string.

SELECT SUBSTRING("automatically",5);
MySQL Substring
SELECT SUBSTRING("automatically",-5);
using FROM and negative position
SELECT artist_name, SUBSTRING(artist_name,4) FROM artists;
FROM on table field
SELECT artist_name, SUBSTRING(artist_name,-4) FROM artists;
ROM on table field and negative position

SUBSTRING(source _string FROM start_position)

This format works same as the earlier one. The only difference is that instead of comma between source string and position the keyword FROM is used.  

SELECT SUBSTRING("automatically" FROM 5);
MySQL Substring
SELECT artist_name, SUBSTRING(artist_name FROM 4) FROM artists;
using FROM and negative position

With Start Position and Length of substring

SUBSTRING(source _string, start_position, length)

In this format, in addition to source string and start position, you need to give the length as the count of characters you want to get from the source string.

SELECT SUBSTRING("automatically",5,4);
SELECT artist_name, SUBSTRING(artist_name,4,2) FROM artists;

SUBSTRING(source _string FROM start_position FOR length)

This variation of Substring function works in the same way as the previous one. Here you use FROM and FOR keywords to define the start position and number of characters. No commas are used in this format of MySQL Substring function.

SELECT SUBSTRING("automatically" FROM 5 FOR 4);
SELECT artist_name, SUBSTRING(artist_name FROM 4 FOR 2) FROM artists;