22,209
社区成员
发帖
与我相关
我的任务
分享
;WITH s AS (
SELECT * FROM #t AS t
CROSS APPLY(VALUES(CONVERT(XML,'<n>'+REPLACE(t.userdata,'-999-','</n><n>')+'</n>'))) c(x)
CROSS APPLY(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS ID, x.n.value('.','varchar(100)') AS m FROM c.x.nodes('n') x(n)) cc
)
SELECT s1.userdata,s1.ID, RIGHT(s1.m, ISNULL(NULLIF(CHARINDEX('-',REVERSE(s1.m)),0)-1,LEN(s1.m)))+'-999-'+LEFT(s2.m,ISNULL(NULLIF(CHARINDEX('-',s2.m),0)-1,LEN(s2.m)))
FROM s AS s1 INNER JOIN s AS s2 ON s1.ID=s2.ID-1 AND s1.userdata=s2.userdata
+--------------------------------------------+----+--------------+
| userdata | ID | |
+--------------------------------------------+----+--------------+
| 123-456-999-78-999-5656-999-4-4565-999-23 | 1 | 456-999-78 |
| 123-456-999-78-999-5656-999-4-4565-999-23 | 2 | 78-999-5656 |
| 123-456-999-78-999-5656-999-4-4565-999-23 | 3 | 5656-999-4 |
| 123-456-999-78-999-5656-999-4-4565-999-23 | 4 | 4565-999-23 |
| 3434534-34534-657-999-345-343-999-1232-666 | 1 | 657-999-345 |
| 3434534-34534-657-999-345-343-999-1232-666 | 2 | 343-999-1232 |
+--------------------------------------------+----+--------------+
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t(userdata VARCHAR(max))
INSERT INTO #t(userdata)VALUES('123-456-999-78-999-5656-999-4-4565-999-23'),('3434534-34534-657-999-345-343-999-1232-666')
;WITH s AS (
SELECT * FROM #t AS t
CROSS APPLY(VALUES(CONVERT(XML,'<n>'+REPLACE(t.userdata,'-999-','</n><n>')+'</n>'))) c(x)
CROSS APPLY(SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) AS ID, x.n.value('.','varchar(100)') AS m FROM c.x.nodes('n') x(n)) cc
)
SELECT s1.userdata, RIGHT(s1.m, ISNULL(NULLIF(CHARINDEX('-',REVERSE(s1.m)),0)-1,LEN(s1.m)))+'-999-'+LEFT(s2.m,ISNULL(NULLIF(CHARINDEX('-',s2.m),0)-1,LEN(s2.m)))
FROM s AS s1 INNER JOIN s AS s2 ON s1.ID=s2.ID-1
+--------------------------------------------+--------------+
| userdata | |
+--------------------------------------------+--------------+
| 123-456-999-78-999-5656-999-4-4565-999-23 | 456-999-78 |
| 123-456-999-78-999-5656-999-4-4565-999-23 | 456-999-345 |
| 123-456-999-78-999-5656-999-4-4565-999-23 | 78-999-5656 |
| 123-456-999-78-999-5656-999-4-4565-999-23 | 78-999-1232 |
| 123-456-999-78-999-5656-999-4-4565-999-23 | 5656-999-4 |
| 123-456-999-78-999-5656-999-4-4565-999-23 | 4565-999-23 |
| 3434534-34534-657-999-345-343-999-1232-666 | 657-999-78 |
| 3434534-34534-657-999-345-343-999-1232-666 | 657-999-345 |
| 3434534-34534-657-999-345-343-999-1232-666 | 343-999-5656 |
| 3434534-34534-657-999-345-343-999-1232-666 | 343-999-1232 |
| 3434534-34534-657-999-345-343-999-1232-666 | 666-999-4 |
+--------------------------------------------+--------------+
select top 100 userdata into #tmp
from channel_self where userdata like '%999%';
WITH
DATA AS(
SELECT value = (select userdata from #tmp)
),
SPLIT AS(
SELECT r = 1,
v = CONVERT(nvarchar(max), LEFT(value, pos - 1)),
value = STUFF(value, 1, pos, N'')
FROM DATA CROSS APPLY( SELECT pos = CHARINDEX('-', DATA.value + '-') )X
WHERE value > ''
UNION ALL
SELECT r = r + 1,
v = CONVERT(nvarchar(max), LEFT(value, pos - 1)),
value = STUFF(value, 1, pos, N'')
FROM SPLIT DATA CROSS APPLY( SELECT pos = CHARINDEX('-', DATA.value + '-') )X
WHERE value > ''
)
SELECT A.v + '-' + B.v + '-' + C.v
FROM SPLIT A, SPLIT B, SPLIT C
WHERE A.r + 1 = B.r AND B.r = C.r - 1
AND B.V IN ('999', '998');
消息 512,级别 16,状态 1,第 2 行
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。WITH
DATA AS(
SELECT value = '123-456-999-78-999-5656-999-4-4565-999-23' UNION ALL
SELECT value = '123-456-998-78-998-5656-998-4-4565-998-23'
),
SPLIT AS(
SELECT k = value, r = 1,
v = CONVERT(nvarchar(max), LEFT(value, pos - 1)),
value = STUFF(value, 1, pos, N'')
FROM DATA CROSS APPLY( SELECT pos = CHARINDEX('-', DATA.value + '-') )X
WHERE value > ''
UNION ALL
SELECT k, r = r + 1,
v = CONVERT(nvarchar(max), LEFT(value, pos - 1)),
value = STUFF(value, 1, pos, N'')
FROM SPLIT DATA CROSS APPLY( SELECT pos = CHARINDEX('-', DATA.value + '-') )X
WHERE value > ''
)
SELECT A.v + '-' + B.v + '-' + C.v
FROM SPLIT A, SPLIT B, SPLIT C
WHERE A.r + 1 = B.r AND B.r = C.r - 1
AND B.V IN ('999', '998')
AND A.k = B.k AND B.k = C.k;
WITH
DATA AS(
SELECT value = '123-456-999-78-999-5656-999-4-4565-999-23'
),
SPLIT AS(
SELECT r = 1,
v = CONVERT(nvarchar(max), LEFT(value, pos - 1)),
value = STUFF(value, 1, pos, N'')
FROM DATA CROSS APPLY( SELECT pos = CHARINDEX('-', DATA.value + '-') )X
WHERE value > ''
UNION ALL
SELECT r = r + 1,
v = CONVERT(nvarchar(max), LEFT(value, pos - 1)),
value = STUFF(value, 1, pos, N'')
FROM SPLIT DATA CROSS APPLY( SELECT pos = CHARINDEX('-', DATA.value + '-') )X
WHERE value > ''
)
SELECT A.v + '-' + B.v + '-' + C.v
FROM SPLIT A, SPLIT B, SPLIT C
WHERE A.r + 1 = B.r AND B.r = C.r - 1
AND B.V IN ('999', '998');
WITH
DATA AS(
SELECT value = '123-456-999-78-999-5656-999-4-4565-999-23',
split = '-999-'
),
SPLIT AS(
SELECT
l = CONVERT(nvarchar(max), REVERSE(LEFT(DATA.value, X.pos - 1))),
value = STUFF(DATA.value, 1, X.pos + X.l - 1, ''),
split
FROM DATA
CROSS APPLY(SELECT pos = CHARINDEX(DATA.split, DATA.value), l = LEN(DATA.split) ) X
WHERE X.pos > 0
UNION ALL
SELECT
l = CONVERT(nvarchar(max), REVERSE(LEFT(DATA.value, X.pos - 1))),
value = STUFF(DATA.value, 1, X.pos + X.l - 1, ''),
split
FROM SPLIT DATA
CROSS APPLY(SELECT pos = CHARINDEX(DATA.split, DATA.value), l = LEN(DATA.split) ) X
WHERE X.pos > 0
)
SELECT REVERSE(LEFT(l, CHARINDEX('-', l + '-') - 1))
+ split
+ LEFT(value, CHARINDEX('-', value + '-') - 1)
FROM SPLIT;
456-999-78
78-999-5656
5656-999-4
4565-999-23
(4 行受影响)
declare @a varchar(max)='123-456-999-78-999-5656-999-4-4565-999-23'
;with cte
as
(select A.*,row_number() over (order by number) as seq,
SUBSTRING(content,number,CHARINDEX('-',content+'-',number)-number) as single_str
from (select @a as content) as A
join master.dbo.spt_values B on CHARINDEX('-','-'+content,number)=number
where type='P')
select B.single_str+'-'+A.single_str+'-'+C.single_str from
(select seq,single_str from cte where single_str='999') as A
join cte B on A.seq=B.seq+1
join cte C on A.seq=C.seq-1