22,209
社区成员
发帖
与我相关
我的任务
分享sql server 去掉字符串中的特殊字符並計算
sql server 去掉字符串中的特殊字符並計算
if not objcet_id ('#test') in null
drop table #test
go
create table #test (name varchar(10))
insert into #test
select N'1;2;3' union all
select N'2;4' union all
select N'1;2' union all
select N'1;3'
1=韻達；2=中通；3=申通；4=順豐
查詢時候想要實現的功能是：
select * from #test
韻達，中通，申通
中通，順豐
韻達，中通
韻達，申通
...全文
请发表友善的回复…
发表回复
独木成林_ 2019-08-14
- 打赏
- 举报
你这种的有缺陷，如果是15;16;这样的数据就有问题了，除非再固定，没有超过10的[/quote]针对于个位数有效的。
二月十六 2019-08-14
- 打赏
- 举报
你这种的有缺陷，如果是15;16;这样的数据就有问题了，除非再固定，没有超过10的
RINK_1 2019-08-14
- 打赏
- 举报
IF OBJECT_ID(N'TEMPDB.DBO.#T1') IS NOT NULL
DROP TABLE #T1
GO
CREATE TABLE #T1(ID INT,NAME NVARCHAR(20))
INSERT INTO #T1
SELECT 1,N'韻達' UNION ALL
SELECT 2,N'中通' UNION ALL
SELECT 3,N'申通' UNION ALL
SELECT 4,N'順豐'
GO
IF OBJECT_ID(N'TEMPDB.DBO.#T2') IS NOT NULL
DROP TABLE #T2
GO
CREATE TABLE #T2 (NAME VARCHAR(100))
INSERT INTO #T2
SELECT '1;2;3' UNION ALL
SELECT '2;4' UNION ALL
SELECT '1;2' UNION ALL
SELECT '1;3'
GO
WITH CTE_1
AS
(SELECT A.*,
SUBSTRING(A.NAME,NUMBER,CHARINDEX(';',A.NAME+';',NUMBER)-NUMBER) AS SINGLE_ID
FROM #T2 A
JOIN MASTER.DBO.spt_values B ON CHARINDEX(';',';'+A.NAME,NUMBER)=NUMBER
WHERE TYPE='P'),
CTE_2
AS
(SELECT A.*,B.NAME AS NAME_CN
FROM CTE_1 A
JOIN #T1 B ON A.SINGLE_ID=B.ID)
SELECT DISTINCT A.NAME,
STUFF((SELECT ','+NAME_CN FROM CTE_2 WHERE A.NAME=NAME FOR XML PATH('')),1,1,'') AS NAME_CN
FROM CTE_2 A
独木成林_ 2019-08-14
- 打赏
- 举报
----如果字段是固定的，可以採用固定寫法
---1=韻達；2=中通；3=申通；4=順豐
if not objcet_id ('#test') in null
drop table #test
go
create table #test (name varchar(10))
insert into #test
select N'1;2;3' union all
select N'2;4' union all
select N'1;2' union all
select N'1;3'
select Replace(Replace(Replace(Replace(name,'1','韻達'),'2','中通'),'3','申通'),'4','順豐') from #test
独木成林_ 2019-08-14
- 打赏
- 举报
還是很感謝你，還有沒有更簡單的方式呢？比如截取的方式進行對比呢？
二月十六 2019-08-14
- 打赏
- 举报
新建函数：
CREATE FUNCTION dbo.f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(
col varchar(max))
AS
BEGIN
DECLARE
@splitlen int
-- 取分隔符的长度, 在分隔符后面加一个字符是为了避免分隔符以空格结束时, 取不到正确的长度
SET @splitlen = LEN(@split + 'a') - 2
-- 如果待分拆的字符串中存在数据分隔符, 则循环取出每个数据项
WHILE CHARINDEX(@split, @s)>0
BEGIN
-- 取第一个数据分隔符前的数据项
INSERT @re VALUES(LEFT(@s, CHARINDEX(@split, @s) - 1))
-- 将已经取出的第一个数据项和数据分隔符从待分拆的字符串中去掉
SET @s = STUFF(@s, 1, CHARINDEX(@split, @s) + @splitlen, '')
END
-- 保存最后一个数据项(最后一个数据项后面没有数据分隔符, 故在前面的循环中不会被处理)
INSERT @re VALUES(@s)
RETURN
end
GO
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[showname] nvarchar(22))
Insert #T
select 1,N'韻達' union all
select 2,N'中通' union all
select 3,N'申通' union all
select 4,N'順豐'
GO
if not object_id(N'Tempdb..#test') is null
drop table #test
go
create table #test (name varchar(10))
insert into #test
select N'1;2;3' union all
select N'2;4' union all
select N'1;2' union all
select N'1;3'
--测试数据结束
;WITH cte AS (
SELECT * FROM (
Select * from #test CROSS APPLY(SELECT * FROM dbo.f_splitSTR(name,';'))t
)t JOIN #T ON t.col=id
)
SELECT
STUFF(( SELECT ',' + showname
FROM cte
WHERE cte.name = a.name
FOR
XML PATH('')
), 1, 1, '') AS value
FROM cte a
GROUP BY a.name 
