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 |
You can get by below query-
SELECT
SUBSTRING_INDEX(mycol,'_',1),
SUBSTRING_INDEX(mycol,'_',-1)
FROM mytable;
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 '%\_%'
select id,
substr(id, 1, instr(id, '_') - 1) as pId,
substr(id, instr(id, '_') + 1, length(id)) as nId
from your_table
jQuery?