Mysql split string in query


I would like to share, How to split string in MySQL query. While we design poor database design, we are forced to construct complex MySQL queries. Luckily MySQL has wonderful string functions. So we are going to use few of those functions to solve the issue.

Task :  Split “1000_33” string by underscore ( _ ) in mysql query and get first part of string.

We usually do this in php using explode function like below.

$arr = explode("_","1000_33"); // splitting string by underscore( _ ).
$arr[0]; // getting first part of string

The same thing we are going do in Mysql Query here.

Solution :

SELECT CASE LOCATE( '_', '1000_33' ) 
WHEN '0'
THEN '1000_33'
ELSE SUBSTRING_INDEX( '1000_33', '_', 1 ) 
END AS first_part_string


LOCATE function is used to figure out is the string has underscore ( _ ) or not. Suppose string doesn’t has underscore, It will return 0 else it will return position of underscore.
So,if string doesn’t have underscore means it will the query will return full string.

SUBSTRING_INDEX is used to split string. LOCATE function ensures string has underscore. So  SUBSTRING_INDEX function will split the first part of string

SUBSTRING_INDEX(  ‘1000_33’,  ‘_’, 1 )


  • Parameter 1 : String
  • Parameter 2 : Search String (Underscore)
  • Parameter 3 : Position of String

Hope this helps someone.

Posted in MySql and tagged by .

About Gowri

I am professional web developer with 8+ years experience. PHP, jQuery, WordPress, Angular and Ionic are my key skills in web development. I am working with strong enthusiastic team with spirit. We provide all web related solution like HTML/CSS development, Web graphic design and Logo.

One thought on “Mysql split string in query

  1. Shaily K


    If you want to parse and fetch later part of string try the SQL below


    Here CAST is a column in MYTABLE and function above will fetch the later last from String column CAST.

    Tokenize r here will be ‘:’

    I hope this helps.

Comments are closed.