sql截取字符串,该怎么处理

sql截取字符串
姓名:叶式|证件类型:身份证|证件号码: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 行受影响)*/ 
 
[解决办法]
SQL code
DECLARE @s NVARCHAR(1000)SET @s=N'姓名:叶式|证件类型:身份证|证件号码:16464679876444145|性别:女|出生日期:1990-6-23|职业类别:第一类|联系电话:12234578941|电子邮件:|邮编:|地址:';WITH CAS( SELECT     a.number AS StratNum,MIN(b.number) AS EndNum,ID=ROW_NUMBER()OVER(ORDER BY a.number) FROM     (SELECT a.number FROM master.dbo.spt_values AS a WHERE type='P' AND CHARINDEX(':',@s,a.number)=number)AS aINNER JOIN     (SELECT a.number FROM master.dbo.spt_values AS a WHERE type='P' AND CHARINDEX('|',@s+'|',a.number)=number) AS b ON a.number<b.numberGROUP BY a.number)SELECT max(CASE WHEN ID=1 THEN SUBSTRING(@s,StratNum+1,EndNum-StratNum-1) END)AS 姓名,max(CASE WHEN ID=2 THEN SUBSTRING(@s,StratNum+1,EndNum-StratNum-1) END)AS 证件类型,max(CASE WHEN ID=3 THEN SUBSTRING(@s,StratNum+1,EndNum-StratNum-1) END)AS 身份证,max(CASE WHEN ID=4 THEN SUBSTRING(@s,StratNum+1,EndNum-StratNum-1) END)AS 性别,max(CASE WHEN ID=5 THEN SUBSTRING(@s,StratNum+1,EndNum-StratNum-1) END)AS 出生日期,max(CASE WHEN ID=6 THEN SUBSTRING(@s,StratNum+1,EndNum-StratNum-1) END)AS 职业类别,max(CASE WHEN ID=7 THEN SUBSTRING(@s,StratNum+1,EndNum-StratNum-1) END)AS 联系电话,max(CASE WHEN ID=8 THEN SUBSTRING(@s,StratNum+1,EndNum-StratNum-1) END)AS 电子邮件,max(CASE WHEN ID=9 THEN SUBSTRING(@s,StratNum+1,EndNum-StratNum-1) END)AS 邮编,max(CASE WHEN ID=10 THEN SUBSTRING(@s,StratNum+1,EndNum-StratNum-1) END)AS 地址FROM C/*姓名    证件类型    身份证    性别    出生日期    职业类别    联系电话    电子邮件    邮编    地址叶式    身份证    16464679876444145    女    1990-6-23    第一类    12234578941                */