Izuzvo Izuzvo - 1 month ago 8
SQL Question

MySQL | update cell based on a function

I have database where the names of the users is like this "Ortega, Rafael" but i need it with the first name in the first position and without the comma.

i found this function that let me substring by a delimemeter:

CREATE FUNCTION SPLIT_STR(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');


but here i got stucked when i tried to write the UPDATE query

UPDATE `names_table` set `name` = CONCAT(
SPLIT_STR(
SELECT `name` FROM `names_table` WHERE <I need help here>,
',',
1
),
" ",
SPLIT_STR(
SELECT `name` FROM `names_table` WHERE <I need help here>,
',',
2
),

Answer

Just use substring_index():

update names_table
    set name = concat_ws(' ',
                         trim(substring_index(name, ',', -1)), 
                         trim(substring_index(name, ',', 1))
                        )
    where name like '%,%';

I'm not sure why you would want to invent another function to do this.

Comments