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.

1
2
3
4
<?php
$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 :

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

Explanation :

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 you 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s