create table tb(id int, value varchar(200))
insert into tb values(1,'姓名:叶式|证件类型:身份证|证件号码:16464679876444145|性别:女|出生日期:1990-6-23|职业类别:第一类|联系电话:12234578941|电子邮件:|邮编:|地址:')
go

SELECT 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, # B
WHERE SUBSTRING('|' + A.[value], B.id, 1) = '|'
) t
group by id

drop 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 c3
union all
select 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 cte
exec('select '+ @sql)
/*
姓名   证件类型   证件号码              性别   出生日期      职业类别   联系电话        电子邮件 邮编   地址
---- ------ ----------------- ---- --------- ------ ----------- ---- ---- ----