解析SQL文件以分隔列 [英] Parse SQL file to separate columns

问题描述

我有一个SQL文件，其中包含很多插入语句(超过3000个).例如

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]');

我已将其导出到excel文档中(我是通过在phpmyadmin中运行查询并导出为ex​​cel文档来完成此操作的).只有一个问题，在这种情况下，您会看到，有两个名称&电子邮件地址被插入人员"中.

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'.

将它们分开显示为Name，email，Name2，email2是多么容易/困难?

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

推荐答案

当有三个电子邮件/名称时该怎么办?显示的数据应该很容易

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

SQL将很难看(因为mysql没有split函数)，但是它将完成工作.

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

拆分表达式来自上的注释mysql文档(搜索拆分).

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

您也可以

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, '');

之后您可以使用

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.

这篇关于解析SQL文件以分隔列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！