MySQL8 SUBSTR() Functions – String Functions

SUBSTR(), This function returns a substring starting from the specified position.

Both MID() and SUBSTR() are synonyms of SUBSTRING().

The forms without a length argument return a substring from a string starting at a specified position. See illustrations 1 and 2.

The forms with a length argument return a substring length characters long from the string, starting at a specified position. See illustrations 3 and 5.

The forms that use FROM are standard SQL syntax.

It is also possible to use a negative(-ve) value for the position. In this case, the beginning of the substring is position characters from the end of the string, rather than the beginning.

A negative(-ve) value may be used for the position in any of the forms of this function. See illustrations 6 and with every illustration, you can see how negative the position value will work.

A value of 0 for position returns an empty string. See illustrations 7.

If length < 1, the result is the empty string. See illustrations 8.

For all forms of substring(), the position of the first character in the string from which the substring is to be extracted is to take into account as 1.

SUBSTR() : Syntax

Below are several ways on how we can use this function, so the all possible ways of syntax’s looks like this:

SUBSTR() : Parameter

[table caption=”” width=”100%” colwidth=”15%|15%|15%|55%” colalign=”left|lef|lef|left”]
Name, Required /Optional, Value Type, Description
string , Required, String , The input string.
position, Required, Numeric, The position from where the substring starts.
length, Optional, Numeric , The length tells the number of characters to return from that starting position.
[/table]

SUBSTR() : Output

[table caption=”” width=”100%” colwidth=”20%|80%” colalign=”left|left”]
Returns,
String, returns a substring starting from the specified position.
[/table]

SUBSTR() : Available from

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 4.0
[/table]

SUBSTR() Example 1 : Basic usage with SUBSTR(string, position)

Below is an example with SUBSTR(string, position):

mysql> SELECT SUBSTR('database mysql', 5) as Output;
+------------+
| Output     |
+------------+
| base mysql |
+------------+
1 row in set (0.00 sec)

With a negative value for the position

mmysql> SELECT SUBSTR('database mysql', -5 ) as Output;
+--------+
| Output |
+--------+
| mysql  |
+--------+
1 row in set (0.07 sec)

SUBSTR() Example 2 : Usage With FROM Clause

Below is an example :

mysql> SELECT SUBSTR('database mysql' FROM 5) as Output;
+------------+
| Output     |
+------------+
| base mysql |
+------------+
1 row in set (0.00 sec)

With a negative value for the position

mysql> SELECT SUBSTR('database mysql' FROM -5 ) as Output;
+--------+
| Output |
+--------+
| mysql  |
+--------+
1 row in set (0.00 sec)

SUBSTR() Example 3 : With Specifying a Length

Below is an example of it.

mysql> SELECT SUBSTR('database mysql', 5, 4) as Output;
+--------+
| Output |
+--------+
| base   |
+--------+
1 row in set (0.00 sec)

With a negative value for the position

mysql> SELECT SUBSTR('database mysql', -10, 3) as Output;
+--------+
| Output |
+--------+
| bas    |
+--------+
1 row in set (0.00 sec)

SUBSTR() Example 4 : NULL arguments

It will return NULL if any of the arguments is NULL. Let’s see the below example.

mysql> SELECT SUBSTR('database mysql' ,NULL, 4 ) as Output1,
              SUBSTR('database mysql',5 ,NULL ) as Output2,
              SUBSTR(NULL, 1, 14 ) as Output3;
+---------+---------+------------------+
| Output1 | Output2 | Output3 |
+---------+---------+------------------+
| NULL    | NULL    | 0x      |
+---------+---------+------------------+
1 row in set (0.00 sec)

SUBSTR() Example 5 : Specify a Length (using the FOR Clause)

Let’s see the below example.

mysql> SELECT SUBSTR('database mysql' FROM 5 FOR 4) as Output;
+--------+
| Output |
+--------+
| base   |
+--------+
1 row in set (0.00 sec)

With a negative value for the position

mysql> SELECT SUBSTR('database mysql' FROM -10 FOR 4) as Output;
+--------+
| Output |
+--------+
| base   |
+--------+
1 row in set (0.00 sec)

SUBSTR() Example 6 : Using a negative value for the position

Let’s see the below example.

mysql> SELECT SUBSTR('database mysql',-5 ) as Output;
+--------+
| Output |
+--------+
| mysql  |
+--------+
1 row in set (0.00 sec)

SUBSTR() Example 7 : Using Zero(0) value for position variable

Let’s see the below example.

mysql> SELECT SUBSTR('database mysql' FROM 0 FOR 4) as Output;
+--------+
| Output |
+--------+
|        |
+--------+
1 row in set (0.00 sec)

SUBSTR() Example 8 : Using Negative value i.e less than 1 for length variable

Let’s see the below example.

mysql> SELECT SUBSTR('database mysql', 10, -5) as Output;
+--------+
| Output |
+--------+
|        |
+--------+
1 row in set (0.00 sec)

See all MySQL String functions MySQL 8 String Functions.

Related articles : LOCATE(), HEX(), CONCAT(), CONCAT_WS() , LOWER(), LTRIM(), INSTR(), POSITION().

PHP Related articles : SUBSTR(), SUBSTR_COUNT(), SUBSTR_COMPARE(), PHP STRING FUNCTIONS(), SUBSTR_REPLACE().