- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- 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 Articles
- 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?
- How to extract date from string in MySQL?
- In MySQL, how can we pad a string with another string?
- How can we eradicate leading and trailing space characters from a string in MySQL?
- How can we remove all the prefixes or suffixes from a given string in MySQL?
- How to find a substring from a string in C#?
