替换两个相同字符之间的字符串 [英] Replace a string between two identical characters

问题描述

我有一个字符串，如

I have a string like

DECLARE @VAR VARCHAR(MAX)
SET @String = '1,1000,"1,1000",0,0,"1,125"'

我希望它为''1 ，1000，11000，0,0，1125''



有可能吗？



我尝试使用

I want it as ''1,1000,"11000",0,0,"1125"''

is it possible?

I tried using the

Replace(SUBSTRING(@String, CHARINDEX('"', @String) + 1,LEN(REPLACE(@String,'"',''))-1),',','')

但它返回1000，11000,0,0，1125



请帮助

but it returning "1000",11000,0,0,"1125

please help

推荐答案

你需要将它分解为元素，这样你才能删除双引号之间的逗号：

You need to break it up into elements so that you only remove teh commas between double quotes:

DECLARE @STRING VARCHAR(MAX)
DECLARE @START VARCHAR(MAX)
DECLARE @MID VARCHAR(MAX)
DECLARE @END VARCHAR(MAX)
DECLARE @I int
SET @STRING = '1,1000,"1,1000",0,0,"1,125"'
SET @I = CHARINDEX('"', @STRING)
SET @START = SUBSTRING(@STRING, 0, @I)
SET @STRING = SUBSTRING(@STRING, @I + 1, LEN(@STRING))
SET @I = CHARINDEX('"', @STRING)
SET @MID = SUBSTRING(@STRING, 0, @I)
SET @END = SUBSTRING(@STRING, @I + 1, LEN(@STRING))
SET @MID = REPLACE(@MID, ',', '')
SET @STRING = @START + @MID + @END

如果有超过WHILE循环，则可能需要添加WHILE循环一个引用的部分 - 我将其作为练习留给读者！ ：笑：

You may have to add a WHILE loop if there is more than one quoted section - I leave that as an exercise for the reader! :laugh:

创建以下功能

Create below function

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(50))
AS
BEGIN
    DECLARE @Value varchar(100)
    WHILE @String is not null
    BEGIN
        SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
        INSERT INTO @RESULTS (Val)
        SELECT @Value
    END
RETURN
END

现在执行以下查询...

Now execute below query...

DECLARE @String VARCHAR(MAX)
DECLARE @S VARCHAR(MAX)
SET @String = '1,1000,"1,1000",0,0,"1,125"'

	select @S = COALESCE(@S, '') +case when charindex('"',val)>0 then case when charindex('"',val) > 1 then '' else '"' end else '' end
				+ 
				replace(val,'"','')
				+ 
				case when charindex('"',val)>0 then case when charindex('"',val) > 1 then '",' else '' end else ',' end  
	from dbo.parsevalues(@string,',')
select substring(@s,0,len(@s))

Happy Coding！

：）

Happy Coding!
:)

这篇关于替换两个相同字符之间的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！