CODEWITHSUNDEEP
mysqlsql
Cameroon P
Cameroon P

Reputation: 129

How can I split a value into multiple columns in mySQL

How can I write a SQL query to do below function. I have a column values with underscore "_". I want to split these values by underscore "_" to create two new columns named pID, nID and keep original ID column intact. 

  Input example          Output example
   ID       |          |  pID | nID    |
1234_591856 |  ==>     | 1234 | 591856 |
12547_15795 |          | 12547| 15795  |
12_185666   |          | 12   | 18566  |

Upvotes: 1

Views: 826

Answers (3)

fthiella
fthiella

Reputation: 49049

If you want to add two new columns to your table you could use ALTER TABLE:

alter table mytable
add column pid varchar(100),
add column nid varchar(100);

then you can update the value of the newly created columns:

update mytable
set
  pid=substring_index(id, '_', 1),
  nid=substring_index(id, '_', -1)
where
  id like '%\_%'

Upvotes: 1

Zafar Malik
Zafar Malik

Reputation: 6844

You can get by below query-

SELECT 
SUBSTRING_INDEX(mycol,'_',1), 
SUBSTRING_INDEX(mycol,'_',-1) 
FROM mytable;

Upvotes: 2

juergen d
juergen d

Reputation: 204746

select id, 
       substr(id, 1, instr(id, '_') - 1) as pId, 
       substr(id, instr(id, '_') + 1, length(id)) as nId
from your_table

SQLFiddle demo

Upvotes: 1

Related Questions