Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/articles/mysql/)
-   -   Substrings Functions in MySQL (http://www.go4expert.com/articles/substrings-functions-mysql-t28573/)

pradeep 12Jun2012 18:10

Substrings Functions in MySQL
 
String manipulation & substring extraction are an important part of any programming language, so it to RDBMS systems like MySQL where it helps to off-load processing at program end by processing string as required at the server end, also sometimes it turns double efficient as the database server caches the result.

Older database systems or version lacked string manipulation functions, but newer day RDBM system have in-built functions to trim, change case, convert between charsets, compress/uncompress. In this article we'll look into substring functions in MySQL, I'll stress on the SUBSTRING_INDEX function which is very handy by seldom made use of by programmers.

SUBSTRING Function



The SUBSTRING function is the most common function found in all programming languages & database servers. This function accepts 2 mandatory arguments and 1 option argument. The first 2 being the string to be worked upon and the offset (the offset is 1-based, i.e. it starts from 1 not 0) to start from respectively, the third and optional argument being the length to be extracted from the offset. Please follow the queries & their output below:

Code:

mysql> SELECT SUBSTRING('Go4expert rocks',3);
+--------------------------------+
| SUBSTRING('Go4expert rocks',3) |
+--------------------------------+
| 4expert rocks                  |
+--------------------------------+
1 row in set (0.03 sec)

mysql> SELECT SUBSTRING('Go4expert rocks',1,9);
+----------------------------------+
| SUBSTRING('Go4expert rocks',1,9) |
+----------------------------------+
| Go4expert                        |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING('Go4expert rocks',-5,5);
+-----------------------------------+
| SUBSTRING('Go4expert rocks',-5,5) |
+-----------------------------------+
| rocks                             |
+-----------------------------------+
1 row in set (0.00 sec)


As you can see in the example a negative offset value set the start of string counting from the end, it's sometimes useful, in a scenario where for example you need the last 5 characters of a string.

SUBSTRING_INDEX Function



This function is similar to the previous function we discussed, in this function the offset is replaced by a delimiter (single or multiple characters), and length is replaced by count - the number of occurrences of the specified delimiter. If the count is a positive value then all characters to left of the specified occurrence of the delimiter is returned and if it's positive all characters to the right is returned. Now, let's understand this with the example below:

Code:

mysql> SELECT SUBSTRING_INDEX('www.go4expert.com','.',2);
+--------------------------------------------+
| SUBSTRING_INDEX('www.go4expert.com','.',2) |
+--------------------------------------------+
| www.go4expert                              |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING_INDEX('www.go4expert.com','.',-2);
+---------------------------------------------+
| SUBSTRING_INDEX('www.go4expert.com','.',-2) |
+---------------------------------------------+
| go4expert.com                               |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.go4expert.com','.',2),'.',-1);
+--------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('www.go4expert.com','.',2),'.',-1) |
+--------------------------------------------------------------------+
| go4expert                                                          |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('get the third word',' ',3),' ',-1);
+---------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('get the third word',' ',3),' ',-1) |
+---------------------------------------------------------------------+
| third                                                               |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)


As you saw in the example, the delimiter acts as a search string to start with and help us manipulate string in MySQL databases much better. I hope this article will help you make the best use of substring functions in MySQL.

References



http://dev.mysql.com/doc/refman/5.5/...ubstring-index


All times are GMT +5.5. The time now is 16:26.