如何得到字段里每次相同符号左右两边的值

easyman123 2017-12-18 08:38:13

现在有这样的值‘123-456-999-78-999-5656-999-4-4565-999-23’ 左右两边的值长度不定，我想得到
456-999-78
78-999-5656
5656-999-4
4565-999-23
求教

...全文

444 18 打赏收藏转发到动态举报

写回复

用AI写文章

18 条回复

切换为时间正序

请发表友善的回复…

发表回复

道素 2017-12-22

打赏
举报

修正一下，上面漏了一个条件


;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 |
+--------------------------------------------+----+--------------+

道素 2017-12-22

打赏
举报


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    |
+--------------------------------------------+--------------+

easyman123 2017-12-19

打赏
举报

你可以加我qq吗 46737225

zjcxc 2017-12-19

打赏
举报

那个图半天显示不出来

zjcxc 2017-12-19

打赏
举报

直接放脚本，这样最准确

easyman123 2017-12-19

打赏
举报

code里面我想得到255-999-289 不是单一一个数字

easyman123 2017-12-19

打赏
举报

这是我没处理的表结构，我想得到这样的表

zjcxc 2017-12-19

打赏
举报

不会改的话，提供示例表和数据

easyman123 2017-12-19

打赏
举报

不是data里赋值给value的时候我就想赋给他一个字段，这个字段里有多行记录 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; [/code][/quote]

easyman123 2017-12-19

打赏
举报

怎么改啊？我改了报错了，我要用在表里不是一串字符

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 行子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

zjcxc 2017-12-19

打赏
举报

多条的情况，加入 key ，比如这个例子分拆多条，value 做为每条记录的 key ( k=value)

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;

zjcxc 2017-12-19

打赏
举报

这个䚱更容易理解，并且处理多个中间值的情况也容易

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');

easyman123 2017-12-19

打赏
举报

大哥我的那一大堆字符串如果在一张表里而且有多条该怎么改啊？
select top 100 userdata into #tmp
from channel_self where userdata like '%999%'

zjcxc 2017-12-19

打赏
举报

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 行受影响)

easyman123 2017-12-19

打赏
举报

我想得到每行的值都返回xxx-999-yyy，是我写得不对还是怎么回事，而且我想返回每行里包含999或者998的，不包含的就不返回

RINK_1 2017-12-18

打赏
举报

引用 2 楼 easyman123 的回复:

大哥为什么我用到表就不行我的表叫#tmp，里面就一个字段

是结果不对，还是运行报错了。你图中的数据，最终的期望结果是什么。

easyman123 2017-12-18

打赏
举报

大哥为什么我用到表就不行我的表叫#tmp，里面就一个字段

RINK_1 2017-12-18

打赏
举报


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