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; // getting first part of string ?>
The same thing we are going do in Mysql Query here.
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.
If you want to parse and fetch later part of string try the SQL below
UPDATE MYTABLE SET CAST = SUBSTR( CAST, POSITION(‘:’ IN CAST )+1);
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.