姓名:叶式|证件类型:身份证|证件号码:16464679876444145|性别:女|出生日期:1990-6-23|职业类别:第一类|联系电话:12234578941|电子邮件:|邮编:|地址:
如何在数据库里面用sql语句操作这个字符串获取数据？
最后把这些数据插入一张表中。。
这张表结构跟字段是一样的。
姓名 证件类型 证件号码 性别 出生日期 职业类别 联系电话 电子邮件 邮编 地址
结果应该是
叶式 身份证 16464679876444145 女 1990-6-23 第一类 12234578941 null null null
如果用程序写的话很简单。但是用语句写。怎么写呢？
------解决方案--------------------
先拆分后,再进行行列转换,以下为sql 2000中使用临时表的方法进行拆分,其他拆分方法见后,只要拆分完毕,就可以使用行列转换达到你的目的.以下只是查询,如果是插入你的某表,则insert into ... select 那个查询即可.
- SQL code
create table tb(id int, value varchar(200))insert into tb values(1,'姓名:叶式|证件类型:身份证|证件号码:16464679876444145|性别:女|出生日期:1990-6-23|职业类别:第一类|联系电话:12234578941|电子邮件:|邮编:|地址:')goSELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b select id , max(case when left(value , charindex(':',value) - 1) = '姓名' and len(value) > len(charindex(':',value)) then substring(value , charindex(':',value) + 1 , len(value)) else '' end) [姓名], max(case when left(value , charindex(':',value) - 1) = '证件类型' and len(value) > len(charindex(':',value)) then substring(value , charindex(':',value) + 1 , len(value)) else '' end) [证件类型], max(case when left(value , charindex(':',value) - 1) = '证件号码' and len(value) > len(charindex(':',value)) then substring(value , charindex(':',value) + 1 , len(value)) else '' end) [证件号码], max(case when left(value , charindex(':',value) - 1) = '性别' and len(value) > len(charindex(':',value)) then substring(value , charindex(':',value) + 1 , len(value)) else '' end) [性别], max(case when left(value , charindex(':',value) - 1) = '出生日期' and len(value) > len(charindex(':',value)) then substring(value , charindex(':',value) + 1 , len(value)) else '' end) [出生日期], max(case when left(value , charindex(':',value) - 1) = '职业类别' and len(value) > len(charindex(':',value)) then substring(value , charindex(':',value) + 1 , len(value)) else '' end) [职业类别], max(case when left(value , charindex(':',value) - 1) = '联系电话' and len(value) > len(charindex(':',value)) then substring(value , charindex(':',value) + 1 , len(value)) else '' end) [联系电话], max(case when left(value , charindex(':',value) - 1) = '电子邮件' and len(value) > len(charindex(':',value)) then substring(value , charindex(':',value) + 1 , len(value)) else '' end) [电子邮件], max(case when left(value , charindex(':',value) - 1) = '邮编' and len(value) > len(charindex(':',value)) then substring(value , charindex(':',value) + 1 , len(value)) else '' end) [邮编], max(case when left(value , charindex(':',value) - 1) = '地址' and len(value) > len(charindex(':',value)) then substring(value , charindex(':',value) + 1 , len(value)) else '' end) [地址]from(SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX('|', A.[value] + '|', B.id) - B.id)FROM tb A, # BWHERE SUBSTRING('|' + A.[value], B.id, 1) = '|') tgroup by iddrop table tb,#
------解决方案--------------------
- SQL code
declare @str nvarchar(max),@sql nvarchar(max)set @str='姓名:叶式|证件类型:身份证|证件号码:16464679876444145|性别:女|出生日期:1990-6-23|职业类别:第一类|联系电话:12234578941|电子邮件:|邮编:|地址:';with cte as(select left(@str,charindex(':',@str)-1) as c1,substring(@str,charindex(':',@str)+1,charindex('|',@str)-charindex(':',@str)-1)c2,right(@str,len(@str)-charindex('|',@str))+'|' as c3union allselect left(c3,charindex(':',c3)-1),substring(c3,charindex(':',c3)+1,charindex('|',c3)-charindex(':',c3)-1),right(c3,len(c3)-charindex('|',c3)) from cte where len(c3)>2)select @sql=isnull(@sql+',','')+''''+c2+''' as ['+c1+']' from cteexec('select '+ @sql)/*姓名 证件类型 证件号码 性别 出生日期 职业类别 联系电话 电子邮件 邮编 地址---- ------ ----------------- ---- --------- ------ ----------- ---- ---- ----叶式 身份证 16464679876444145 女 1990-6-23 第一类 12234578941 (1 行受影响)*/