MySQL Tutorial/String Functions/SUBSTRING

SUBSTRING("ABCDEFGHIJK", -3);

   <source lang="sql">

mysql> mysql> SELECT SUBSTRING("ABCDEFGHIJK", -3); +------------------------------+ | SUBSTRING("ABCDEFGHIJK", -3) | +------------------------------+ | IJK | +------------------------------+ 1 row in set (0.00 sec) mysql></source>

SUBSTRING("ABCDEFGHIJK",;

   <source lang="sql">

mysql> mysql> SELECT SUBSTRING("ABCDEFGHIJK", -5, 3); +---------------------------------+ | SUBSTRING("ABCDEFGHIJK", -5, 3) | +---------------------------------+ | GHI | +---------------------------------+ 1 row in set (0.00 sec) mysql></source>

SUBSTRING("ABCDEFGHIJK",5,6);

   <source lang="sql">

mysql> mysql> SELECT SUBSTRING("ABCDEFGHIJK",5,6); +------------------------------+ | SUBSTRING("ABCDEFGHIJK",5,6) | +------------------------------+ | EFGHIJ | +------------------------------+ 1 row in set (0.00 sec) mysql></source>

SUBSTRING("ABCDEFGHIJK" FROM 4);

   <source lang="sql">

mysql> mysql> mysql> SELECT SUBSTRING("ABCDEFGHIJK" FROM 4); +---------------------------------+ | SUBSTRING("ABCDEFGHIJK" FROM 4) | +---------------------------------+ | DEFGHIJK | +---------------------------------+ 1 row in set (0.00 sec) mysql></source>

SUBSTRING("ABCDEFGHIJK" FROM -4 FOR 2);

   <source lang="sql">

mysql> mysql> mysql> SELECT SUBSTRING("ABCDEFGHIJK" FROM -4 FOR 2); +----------------------------------------+ | SUBSTRING("ABCDEFGHIJK" FROM -4 FOR 2) | +----------------------------------------+ | HI | +----------------------------------------+ 1 row in set (0.00 sec) mysql></source>

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

The forms without a len argument return a substring from string str starting at position pos.

The forms with a len argument return a substring len characters long from string str, starting at position pos.

If pos is a negative value, the beginning of the substring is from the end of the string.

The position of the first character is reckoned as 1.

If len is less than 1, the result is the empty string.

SUBSTR() is a synonym for SUBSTRING().

MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).

   <source lang="sql">

mysql> SELECT SUBSTRING("ABCDEFGHIJK",5); +----------------------------+ | SUBSTRING("ABCDEFGHIJK",5) | +----------------------------+ | EFGHIJK | +----------------------------+ 1 row in set (0.00 sec) mysql></source>

Use SUBSTRING with data in a table

   <source lang="sql">

mysql> mysql> CREATE TABLE Employee(

   ->     id            int,
   ->     first_name    VARCHAR(15),
   ->     last_name     VARCHAR(15),
   ->     start_date    DATE,
   ->     end_date      DATE,
   ->     salary        FLOAT(8,2),
   ->     city          VARCHAR(10),
   ->     description   VARCHAR(15)
   -> );

Query OK, 0 rows affected (0.02 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->              values (1,"Jason",    "Martin",  "19960725",  "20060725", 1234.56, "Toronto",  "Programmer");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(2,"Alison",   "Mathews",  "19760321", "19860221", 6661.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");

Query OK, 1 row affected (0.01 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");

Query OK, 1 row affected (0.02 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> SELECT SUBSTRING(first_name,5)

   -> FROM employee;

+-------------------------+ | SUBSTRING(first_name,5) | +-------------------------+ | n | | on | | s | | a | | rt | | a | | d | | s | +-------------------------+ 8 rows in set (0.02 sec) mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec) mysql></source>