How to split a column in Mysql?

Question

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?

Shaikh Farooque · Accepted Answer · 2012-04-20 03:54:46Z

2

Kindly check with the following

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

answered Apr 20, 2012 at 3:54

Shaikh Farooque

2,6402 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sergey Kalinichenko · Accepted Answer · 2012-04-20 03:58:07Z

1

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

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

answered Apr 20, 2012 at 3:58

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

1 Comment

zhouzuan2k Over a year ago

Thx. It's a beautiful solution.

luis · Accepted Answer · 2012-04-20 04:47:28Z

0

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;

answered Apr 20, 2012 at 4:47

luis

1

Collectives™ on Stack Overflow

How to split a column in Mysql?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related