How can we extract a substring from a string in MySQL?

MySQL SUBSTRING() function can be used to extract a substring from a string. Basically SUBSTRING() returns a substring with a given length from a string starting at a specific position. It has various forms as follows −

• 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. The forms that use FROM are the standard MySQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.

mysql> SELECT SUBSTRING('Quadratically',5);
+---------------------------------------------------------+
| SSUBSTRING('Quadratically',5)                           |
+---------------------------------------------------------+
| ratically                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('foobarbar' FROM 4);
+---------------------------------------------------------+
| SUBSTRING('foobarbar' FROM 4)                           |
+---------------------------------------------------------+
| barbar                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('Quadratically',5,6);
+---------------------------------------------------------+
| SUBSTRING('Quadratically',5,6)                          |
+---------------------------------------------------------+
| ratica                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> Select SUBSTRING('foobarbar',-4);
+---------------------------+
| SUBSTRING('foobarbar',-4) |
+---------------------------+
| rbar                      |
+---------------------------+
1 row in set (0.05 sec)

Other than SUBSTRING() function, MID() and SUBSTR() functions are also used to extract a substring from a string. They both are synonyms of SUBSTRING() function.

Samual Sam

Published on 20-Feb-2018 14:30:48