从逗号分隔的字符串中删除@，$

debugcn 发表于 Dev

下雨

我有一个查询

declare @strString varchar(500)
set @strString ='Terminal$Attr1,Attr2,Attr3,Attr4,Attr5,Attr6,@Connector$Con1,Con2,Con3,Con4,@Wire$W1,W2,W3,W4,W5,' 

;WITH StrCTE(start, stop) AS
    (
      SELECT  1, CHARINDEX('$' , @strString )
      UNION ALL
      SELECT  stop + 1, CHARINDEX(',' ,@strString  , stop + 1)
      FROM StrCTE
      WHERE stop > 0
    )
    SELECT   SUBSTRING(@strString , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS stringValue
    FROM StrCTE
    where SUBSTRING(@strString , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END)<>''

这为我提供了输出：

stringValue
Terminal  
Attr1  
Attr2  
Attr3  
Attr4  
Attr5  
Attr6   
@Connector$Con1  
Con2  
Con3  
Con4  
@Wire$W1  
W2  
W3  
W4  
W5

但我需要的输出为：

stringValue  
Terminal  
Attr1  
Attr2  
Attr3  
Attr4  
Attr5  
Attr6  
Connector  
Con1  
Con2  
Con3  
Con4  
Wire  
W1  
W2  
W3  
W4  
W5

Jaydip贾达夫

尝试在此进行更改 CTE

declare @strString varchar(500)
set @strString ='Terminal$Attr1,Attr2,Attr3,Attr4,Attr5,Attr6,@Connector$Con1,Con2,Con3,Con4,@Wire$W1,W2,W3,W4,W5,' 

Set @strString=REPLACE(REPLACE(@strString,'$',','),'@','')--Added

;WITH StrCTE(start, stop) AS

(
  SELECT  1, CHARINDEX(',' , @strString )
  UNION ALL
  SELECT  stop + 1, CHARINDEX(',' ,@strString  , stop + 1)
  FROM StrCTE
  WHERE stop > 0
)  
SELECT   SUBSTRING(@strString , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS stringValue
FROM StrCTE
where SUBSTRING(@strString , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END)<>''

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。