日期:2014-05-17 浏览次数:20605 次
CREATE TABLE #Tmp
(
BerthCode VARCHAR(20) ,-- 车位地址
BerthStatus VARCHAR(20)-- 车位位状
)
CREATE TABLE test (id INT,[key] NVARCHAR(256))
INSERT INTO test
VALUES(1,'车位地址1,车态位状1
------解决方案--------------------
车位地址2,车位状态2
------解决方案--------------------
车位地址n,车位状态n')
;WITH cte AS (
SELECT distinct
SUBSTRING([key],number,CHARINDEX('
------解决方案--------------------
',[key]+'
------解决方案--------------------
',number)-number) as [key]
from
test a,master..spt_values
where
number >=1 and number<len([key])
and type='p'
and substring('
------解决方案--------------------
'+[key],number,1)='
------解决方案--------------------
')
SELECT SUBSTRING([key],1,CHARINDEX(',',[key],1)-1) AS BerthCode,SUBSTRING([key],CHARINDEX(',',[key],1)+1,LEN([key])) AS [BerthStatus]
FROM cte
/*
BerthCode BerthStatus