Substrings Functions in MySQL

Discussion in 'MySQL' started by pradeep, Jun 12, 2012.

  1. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,645
    Likes Received:
    87
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    http://blog.pradeep.net.in
    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/en/string-functions.html#function_substring-index
     
    Alex.Gabriel likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice