日期:2014-05-17 浏览次数:20387 次
USE test
GO
-->生成表tb
if object_id('tb') is not null
drop table tb
Go
Create table tb([数据格式] nvarchar(4000),ID INT,[user] NVARCHAR(50),[password] NVARCHAR(50),email NVARCHAR(50))
Insert into tb([数据格式])
Select '8 testsql1 3867255b2ab455916c6cf1b08a081524'
Union all Select '9 testsql2 75856dbe66d249e64ae0685bf9917cfd 16535@qq.com'
Union all Select '10 testsql3 e10adc3949ba593bbe56e057f20f883e mdissal@yahoo.com.cn'
Union all Select '11 testsql4 aec35988f4b24bdaa64bea491ef28716'
DECLARE @sql NVARCHAR(MAX)
IF object_id('tempdb..#')IS NOT NULL
DROP TABLE #
SELECT 数据格式 AS 数据格式2,* INTO # FROM tb
WHILE EXISTS(SELECT 1 FROM # WHERE CHARINDEX(SPACE(1)+SPACE(1),数据格式2)>0)
UPDATE # SET 数据格式2=REPLACE(数据格式2,SPACE(1)+SPACE(1),SPACE(1))
UPDATE # SET 数据格式2=数据格式2+REPLICATE(SPACE(1),3-LEN(数据格式2)+LEN(REPLACE(数据格式2,SPACE(1),'')))
SELECT @sql=ISNULL(@sql+' Union all ','')+'Select N'''+数据格式2+''',N'''+数据格式+''',N'''+REPLACE(数据格式2,SPACE(1),''',N''')+'''' FROM #
EXEC (N';With t ([数据格式2],[数据格式],ID,[user],[password],email) As ( '+@sql+N' ) Update a Set ID=b.ID,[user]=b.[user],[password]=b.[password],email=b.email From tb As a Inner Join t As b On a.[数据格式]=b.[数据格式] ')
SELECT * FROM tb
/*
数据格式 ID user password email
--------------