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.

<?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 :

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 someone.