建立函数
ALTER FUNCTION dbo.f_splite
(
@s VARCHAR(8000) , --待分拆的字符串
@split VARCHAR(10) --数据分隔符
)
RETURNS @re TABLE (id INT,col VARCHAR(100) )
AS
BEGIN
DECLARE @splitlen INT
DECLARE @i INT = 1
SET @splitlen = LEN(@split + 'a') - 2
WHILE CHARINDEX(@split, @s) > 0
BEGIN
INSERT @re
VALUES ( @i,LEFT(@s, CHARINDEX(@split, @s) - 1) )
SET @s = STUFF(@s, 1, CHARINDEX(@split, @s) + @splitlen, '')
SET @i = @i +1
END
INSERT @re
VALUES ( @i,@s )
RETURN
END
GO
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[mation] nvarchar(79))
Insert #T
select 42,N'测试张三[Auth:BASIC:1,ADMIN:SAME_ACCOUNT_ID:0,CUSTOMER:BASEC:1]' union all
select 49,N'李四测试[Customer:SAME_Account_id:1]'
Go
--测试数据结束
SELECT #T.id,REPLACE(t.col,':',',') AS [mation]
FROM #T
CROSS APPLY ( SELECT *
FROM dbo.f_splite(
SUBSTRING(
mation, CHARINDEX('[', mation) + 1, CHARINDEX(']', mation) - CHARINDEX('[', mation) - 1),
',') ) t;
ALTER FUNCTION dbo.f_splite
(
@s VARCHAR(8000) , --待分拆的字符串
@split VARCHAR(10) --数据分隔符
)
RETURNS @re TABLE (id INT,col VARCHAR(100) )
AS
BEGIN
DECLARE @splitlen INT
DECLARE @i INT = 1
SET @splitlen = LEN(@split + 'a') - 2
WHILE CHARINDEX(@split, @s) > 0
BEGIN
INSERT @re
VALUES ( @i,LEFT(@s, CHARINDEX(@split, @s) - 1) )
SET @s = STUFF(@s, 1, CHARINDEX(@split, @s) + @splitlen, '')
SET @i = @i +1
END
INSERT @re
VALUES ( @i,@s )
RETURN
END
GO
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[mation] nvarchar(79))
Insert #T
select 42,N'测试张三[Auth:BASIC:1,ADMIN:SAME_ACCOUNT_ID:0,CUSTOMER:BASEC:1]' union all
select 49,N'李四测试[Customer:SAME_Account_id:1]'
Go
--测试数据结束
SELECT #T.id,REPLACE(t.col,':',',') AS [mation]
FROM #T
CROSS APPLY ( SELECT *
FROM dbo.f_splite(
SUBSTRING(
mation, CHARINDEX('[', mation) + 1, CHARINDEX(']', mation) - CHARINDEX('[', mation) - 1),
',') ) t;
DECLARE @t TABLE(id INT, context NVARCHAR(200));INSERT @t(id, context)
VALUES(1, N'AAA[A:B:C,1:2:3,4:5:6]'),
(2, N'FFFF[AAAA:BBBB:CCCC,1111:2222:3333,4444:5555:6666]'),(3,N'[天狼:暗夜:五星]');WITH t AS (SELECT a.id,
b.VALUE,
b.seq,
b.seq2
FROM @t a
CROSS APPLY(SELECT y.VALUE,
x.id AS seq,
y.seq2
FROM dbo.Split(
SUBSTRING(
a.context, CHARINDEX('[', a.context) + 1,
LEN(a.context) - CHARINDEX('[', a.context) - 1), ',') x
CROSS APPLY(SELECT VALUE, id AS seq2 FROM dbo.Split(x.VALUE, ':') )y )b )
SELECT id,
(SELECT MAX(b.VALUE) FROM t b
WHERE a.id = b.id AND a.seq = b.seq AND b.seq2 = 1) '1',
(SELECT MAX(b.VALUE) FROM t b
WHERE a.id = b.id AND a.seq = b.seq AND b.seq2 = 2) '2',
(SELECT MAX(b.VALUE) FROM t b
WHERE a.id = b.id AND a.seq = b.seq AND b.seq2 = 3) '3'
FROM t a
GROUP BY id,
a.seq;
SELECT p.id, p.[3], p.[2], p.[1] from (SELECT a.id,
b.VALUE,
b.seq,
b.seq2
FROM @t a
CROSS APPLY(SELECT y.VALUE,
x.id AS seq,
y.seq2
FROM dbo.Split(
SUBSTRING(
a.context, CHARINDEX('[', a.context) + 1,
LEN(a.context) - CHARINDEX('[', a.context) - 1), ',') x
CROSS APPLY(SELECT VALUE, id AS seq2 FROM dbo.Split(x.VALUE, ':') )y )b ) t
PIVOT (MAX(t.VALUE) FOR seq2 IN ([1],[2],[3])) p
ORDER BY id,p.seq
函数建了吗
函数建了吗肯定的啊 老哥
2014,但是这个应该不是很低的版本都能用
2014,但是这个应该不是很低的版本都能用 2000 2005 08都不行 要不是没申明变量 要不是SUB那边出错