LinuxQuestions.org - MySQL splitting a string with a delimiter and taking the 1st value and update

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - MySQL splitting a string with a delimiter and taking the 1st value and update (https://www.linuxquestions.org/questions/programming-9/mysql-splitting-a-string-with-a-delimiter-and-taking-the-1st-value-and-update-617710/)

MySQL splitting a string with a delimiter and taking the 1st value and update

Hey everyone,

I am trying to take a string like "Brown, James" sans quotes and taking the substring James and tossing it into the column first_name.

Here is what I think would do it, but I know its not the right way because it doesn't work.

insert into instructors (first_name) SELECT SUBSTRING_INDEX(instructor, ' , ', 1) FROM srs_data;

Any help would be great,

Ronnie

I noticed in your code you actually have:

Code:

' , '

with space before and after comma. Could just be the way you typed it here, but don't do that.

Can you post your mysql output?

Ok it will do the last_name like I want now, however I want it to update the table: I get a syntax error, so I think I am being too clever for MySQL.. php you can get away with murder ;)

update instuctors WHERE last_name = SELECT DISTINCT SUBSTRING_INDEX(instructor, ', ', 1) FROM instructors;

1st, maybe wanna think about posting actual phone numbers on the net. This page will be archived and be around forever, maybe use some asterisks.

2nd, I have a suggestion, but make a copy of the data and work on that. I haven't used mysql in a long time, and I have no idea if this will work. What you're doing there is adding new rows, when it sounds like you need to be updating rows.

Try:

Code:

UPDATE instructors SET last_name=SUBSTRING_INDEX(instructor, ", ", 1), first_name=SUBSTRING_INDEX(instructor, ", ", -1)

WHERE instructor IS NOT NULL;

Edit
----
Also use code tags.

Quote:

Originally Posted by shakezilla (Post 3041514)

Code:

UPDATE instructors SET last_name=SUBSTRING_INDEX(instructor, ", ", 1), first_name=SUBSTRING_INDEX(instructor, ", ", -1)

WHERE instructor IS NOT NULL;

Edit
----
Also use code tags.

No worries, they aren't real anyways, you'll wind up in alabama ;) Thanks for the help though. Awesome actually, nice and clean.

So one more quick question. I would trim the instructor column to get rid of the ", " and then to add a "@hotmail.com" I would use concatonation?

Code:

UPDATE students SET email = LOWER(CONCAT(LEFT(SUBSTRING_INDEX(first_name, ", ", 1),1),SUBSTRING_INDEX(last_name, ", ", -1),"@shoes.ca")) WHERE first_name IS NOT NULL;

Gotter :) Made it lowercase, shifted letters and concatenated on the email prefix. :) Thanks Man