27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #Tmp
(
BerthCode VARCHAR(20) ,-- 车位地址
BerthStatus VARCHAR(20)-- 车位位状
)
declare @str varchar(200)
set @str='车位地址1,车态位状1|车位地址2,车位状态2|车位地址13,车位状态13|车位地址n,车位状态n'
if OBJECT_ID('tmp',N'U')>0 drop table tb
create table tmp(BerthCode VARCHAR(20),BerthStatus VARCHAR(20))
set @str=LTRIM(RTRIM(@str))
if RIGHT(@str,1)<>'|'
set @str=@str+'|'
while CHARINDEX('|',@str)>0
begin
insert into tmp(BerthCode,BerthStatus)
select SUBSTRING(@str,1,CHARINDEX(',',@str)-1),SUBSTRING(@str,CHARINDEX(',',@str)+1,CHARINDEX('|',@str)-1-CHARINDEX(',',@str))
set @str=STUFF(@str,1,CHARINDEX('|',@str),'')
end
select * from tmp
drop table tmp
--结果
BerthCode BerthStatus
-------------------- --------------------
车位地址1 车态位状1
车位地址2 车位状态2
车位地址13 车位状态13
车位地址n 车位状态n
(4 行受影响)
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
------------- -------------------
车位地址1 车态位状1
车位地址2 车位状态2
车位地址n 车位状态n
*/