CODEWITHSUNDEEP
mysql
Sharpeye500
Sharpeye500

Reputation: 9063

Comma separated values in mysql

I have a column:

Name
John,Doe

I want the output from select as

First name Last name
John       Doe

How to remove comma separated data in mysql?

Upvotes: 0

Views: 677

Answers (3)

Rikin Patel
Rikin Patel

Reputation: 9383

SELECT
'FirstName, MiddleName, LastName',
SUBSTRING_INDEX('FirstName, MiddleName, LastName', ',', 1) 
    AS `First Name`,
SUBSTRING_INDEX(SUBSTRING_INDEX('FirstName, MiddleName, LastName', ',', 2), ',', -1) 
    AS  `Middle Name`,
SUBSTRING_INDEX(SUBSTRING_INDEX('FirstName, MiddleName, LastName', ',', 3), ',', -1) 
    AS  `Last Name`

See output of this query here.

Upvotes: 0

Robin Michael Poothurai
Robin Michael Poothurai

Reputation: 5664

You can try below query to do that,

select SUBSTRING_INDEX('John,Doe', ',', 1) as str1, SUBSTRING_INDEX('John,Doe', ',', -1) as str2

Upvotes: 0

duffymo
duffymo

Reputation: 308743

I would say that this breaks first normal form. Don't store two data items in a single column. If you want two items, use two columns.

If you must, please refer to this documentation. As you can see, MySQL doesn't have a string split function.

Upvotes: 1

Related Questions