Parse SQL file to separate columns

Question

I have a sql file which has a lot of insert statements (over 3000+). E.g.

insert into `pubs_for_client` (`ID`, `num`, `pub_name`, `pub_address`, `publ_tele`, `publ_fax`, `pub_email`, `publ_website`, `pub_vat`, `publ_last_year`, `titles_on_backlist`, `Personnel`) values('7','5','4TH xxxx xxxx','xxxx xxxx, 16 xxxxx xxxxx, xxxxxxx, We','111111111','1111111111','[e-mail]','www.example.net','15 675 4238 14',NULL,NULL,'Jane Bloggs(Sales Contact:)[e-mail],Joe Bloggs(Other Contact:)[e-mail]');

I have exported this into an excel document (I did this through running the query in phpmyadmin, and exporting for an excel document). There's just one problem, as you can see in this case, there are two names & email addresses being inserted into 'Personnel'.

How easy/difficult would it be to seperate these out to display as Name, email, Name2, email2?

Answer 1

What about when there are three e-mails/names? With shown data it should be easy to do

select replace(substring(substring_index(`Personnel`, ',', 1),length(substring_index(`Personnel`, ',', 1 - 1)) + 1), ',', '') personnel1,
       replace(substring(substring_index(`Personnel`, ',', 2),length(substring_index(`Personnel`, ',', 2 - 1)) + 1), ',', '') personnel2,
from `pubs_for_client`

The above will split the Personnel column on delimiter ,.
You can then split these fields on delimiter ( and ) to split personnel into name, position and e-mail

The SQL will be ugly (because mysql does not have split function), but it will get the job done.

The split expression was taken from comments on mysql documentation (search for split).

You can also

CREATE FUNCTION strSplit(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, '');

After which you can user

select strSplit(`Personnel`, ',', 1), strSplit(`Personnel`, ',', 2)
from `pubs_for_client`

You could also create your own function that will extract directly names and e-mails.

Answer 2

As bogeymin has already said - either get the data to CSV (or convert it easily from Excel) to manipulate it. If you're on Windows, then have a look at using Notepad++ to break apart the last column.

Or... (and I'd probably do this), insert it into the database as it is (even if you insert into a dummy field, not the one you eventually want to use), then use the string manipulation functions in your varient of SQL to make either update statements, or more insert statements (whatever you need). Cerainly, MS-SQL Server can do this using things like SUBSTRING, PATINDEX etc etc...