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 10:00:48

• Related Questions & Answers
• How can we extract a substring from the value of a column in MySQL table?
• How can we get substring from a string in Python?
• How to extract a substring from inside a string in Python?
• How to extract certain substring from a string using Java?
• How can we replace all the occurrences of a substring with another substring within a string in MySQL?
• In MySQL, how can we insert a substring at the specified position in a string?
• How can we extract the Year and Month from a date in MySQL?
• How to extract substring from a sting starting at a particular position in MySQL?
• How can we extract the numbers from an input string in Java?
• In how many ways can we find a substring inside a string in javascript?
• In MySQL, how can we pad a string with another string?
• How to extract date from string in MySQL?
• How to find a substring from a string in C#?
• How can we remove a column from MySQL table?
• How can we revoke privileges from a MySQL user?