- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
- 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?