Mysql split string in query

on

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.

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

    Hi

    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.

Comments are closed.