LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 01-31-2008, 12:19 PM   #1
Lantzvillian
Member
 
Registered: Oct 2007
Location: BC, Canada
Distribution: Fedora, Debian
Posts: 210

Rep: Reputation: 41
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
 
Old 01-31-2008, 12:38 PM   #2
shakezilla
Member
 
Registered: Jun 2005
Location: U.S.
Distribution: xubuntu 8.10, linux 2.6.27-11-generic
Posts: 78

Rep: Reputation: 16
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?
 
Old 01-31-2008, 12:46 PM   #3
Lantzvillian
Member
 
Registered: Oct 2007
Location: BC, Canada
Distribution: Fedora, Debian
Posts: 210

Original Poster
Rep: Reputation: 41
mmmh yeah accident.

+---------------+--------------+-----------+---------------+----------------+---------------+
| instructor_id | first_name | last_name | office_number | office_phone | instructor |
+---------------+--------------+-----------+---------------+----------------+---------------+
| 1 | NULL | NULL | Office Num | OffiicePhone | Instructor |
| 2 | NULL | NULL | 180/222 | (205) 740-7661 | Avery, Terry |
| 3 | NULL | NULL | 180/226 | (205) 740-6116 | Dutchuk, Mark |
| 4 | NULL | NULL | 180/227 | (205) 740-6777 | Croft, David |
| 5 | NULL | NULL | 180/226 | (205) 740-6777 | Croft, David |
| 6 | NULL | NULL | 180/224 | (205) 740-6777 | Croft, David |
| 7 | NULL | NULL | 180/224 | (205) 740-6778 | Loewen, John |
| 17 | Avery, Terry | NULL | NULL | NULL | NULL |
| 16 | Avery, Terry | NULL | NULL | NULL | NULL |
| 15 | Avery, Terry | NULL | NULL | NULL | NULL |
+---------------+--------------+-----------+---------------+----------------+---------------+

Instructor_id 1-7 were there before as a test. 17 + are the result of that statement.
 
Old 01-31-2008, 12:53 PM   #4
Lantzvillian
Member
 
Registered: Oct 2007
Location: BC, Canada
Distribution: Fedora, Debian
Posts: 210

Original Poster
Rep: Reputation: 41
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;
 
Old 01-31-2008, 02:05 PM   #5
Lantzvillian
Member
 
Registered: Oct 2007
Location: BC, Canada
Distribution: Fedora, Debian
Posts: 210

Original Poster
Rep: Reputation: 41
Fixed part of it:
mysql> insert into instructors (last_name) SELECT SUBSTRING_INDEX(instructor, ', ', 1) FROM instructors;
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> select * from instructors;
+---------------+------------+------------+---------------+----------------+---------------+
| instructor_id | first_name | last_name | office_number | office_phone | instructor |
+---------------+------------+------------+---------------+----------------+---------------+
| 1 | NULL | NULL | Office Num | OffiicePhone | Instructor |
| 2 | NULL | NULL | 180/222 | (205) 740-7661 | Avery, Terry |
| 3 | NULL | NULL | 180/226 | (205) 740-6116 | Dutchuk, Mark |
| 4 | NULL | NULL | 180/227 | (205) 740-6777 | Croft, David |
| 5 | NULL | NULL | 180/226 | (205) 740-6777 | Croft, David |
| 6 | NULL | NULL | 180/224 | (205) 740-6777 | Croft, David |
| 7 | NULL | NULL | 180/224 | (205) 740-6778 | Loewen, John |
| 331 | NULL | Loewen | NULL | NULL | NULL |
| 330 | NULL | Croft | NULL | NULL | NULL |
| 329 | NULL | Croft | NULL | NULL | NULL |
| 328 | NULL | Croft | NULL | NULL | NULL |
| 327 | NULL | Dutchuk | NULL | NULL | NULL |
| 326 | NULL | Avery | NULL | NULL | NULL |
| 325 | NULL | Instructor | NULL | NULL | NULL |
+---------------+------------+------------+---------------+----------------+---------------+
14 rows in set (0.00 sec)

Its still not doing what I want though, any suggestions?
 
Old 01-31-2008, 03:16 PM   #6
shakezilla
Member
 
Registered: Jun 2005
Location: U.S.
Distribution: xubuntu 8.10, linux 2.6.27-11-generic
Posts: 78

Rep: Reputation: 16
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.

Last edited by shakezilla; 01-31-2008 at 03:19 PM. Reason: vbcode
 
Old 01-31-2008, 11:18 PM   #7
Lantzvillian
Member
 
Registered: Oct 2007
Location: BC, Canada
Distribution: Fedora, Debian
Posts: 210

Original Poster
Rep: Reputation: 41
Wink

Quote:
Originally Posted by shakezilla View Post
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.
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?
 
Old 01-31-2008, 11:57 PM   #8
Lantzvillian
Member
 
Registered: Oct 2007
Location: BC, Canada
Distribution: Fedora, Debian
Posts: 210

Original Poster
Rep: Reputation: 41
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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Wish me luck, Im taking CLP on august 31st and my drivers license on september 1st.. Fraudulent Linux - Certification 3 05-06-2006 08:34 PM
LXer: Taking Advantage Of Open Source PHP MySQL Applications LXer Syndicated Linux News 0 12-28-2005 08:16 PM
Divide up lines with string delimiter elmu Programming 3 10-07-2005 08:48 AM
KDE wont start after 1st patch update benlm54 SUSE / openSUSE 2 07-16-2005 11:32 PM
Bash Script String Splitting MurrayL Linux - Newbie 1 09-21-2004 03:20 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 04:40 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration