CODEWITHSUNDEEP
mysqlstringsplit
zhouzuan2k
zhouzuan2k

Reputation: 167

How to split a column in Mysql?

I have a column which value is combine two elements, Just like "Class_Name"。For example , "Websites_Google"。But for some historical reasons, there have some special value which have no "Class_" prefix, like "yahoo".

Now I need to extract the class from columns. If there is no "class_" prefix , i expect return "" . I just try SUBSTRING_INDEX functions like this:

select SUBSTRING_INDEX(column1, '_', 1) .......

It works very well when the value have "class_", but in the no "class_" situation, it return the whole value, "yahoo" return "yahoo", not the empty string which i want.

How to solve it?

Upvotes: 2

Views: 616

Answers (3)

luis
luis

Reputation: 1

You will have to create a mysql function. Here's a working example:

DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`split_class` $$
CREATE FUNCTION `test`.`split_class` (`class` varchar(25)) RETURNS varchar(25)
BEGIN

DECLARE `tmp1` varchar(25);
DECLARE `tmp2` varchar(25);
DECLARE `ret` varchar(25);

  set `tmp1` = SUBSTRING_INDEX(`class`, '_', 1);
  set `tmp2` = SUBSTRING_INDEX(`class`, '_', -1);
  set `ret` = '';

  IF `tmp1` != `tmp2`
    then set `ret` = `tmp1`;
  END IF;

  RETURN `ret`;
END $$

DELIMITER ;

Then, try the select

SELECT split_class(last) FROM test.`table` t;

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

You can use IF to check if the underscore is there:

select if(locate('_',column1)=0,'',SUBSTRING_INDEX(column1, '_', 1))
from mytable

Upvotes: 1

Shaikh Farooque
Shaikh Farooque

Reputation: 2630

Kindly check with the following

select case when CHARINDEX ('_', column1)>0 then SUBSTRING_INDEX(column1, '_', 1) else '' end .....

Upvotes: 2

Related Questions