如何替换使用SQL存储过程作为输入参数的字符串中的字符

Question

我有一个有两列的表，一列用于存储要替换的字符，另一列用于存储替换字符。

让我的表的行(每列用'|'分隔)

1. rnrn | rn
2. rn | n

如何编写一个存储过程，它接受字符串作为参数，并用这些列值替换字符串的字符。(例如，如果一个字符串包含rn，那么它必须被n替换)

我已经尝试了游标方法，并使用字符串执行了这个过程:-

EXEC DBO.SP_REPLACE 'Hello!rnThis is for the testing ofrnrnString
replacementrnwith charactersrnrnand special charactersrn'

结果字符串:-

Hello!nThis is for the testing ofnString replacementnwith 
charactersnand special charactersn

实际上我想用一个替换来替换'FIRST occurrence '。一旦替换完成，应避免进一步替换已替换的字符串。

所以最终期望的o/p是-

Hello!nThis is for the testing ofrnString replacementnwith 
charactersrnand special charactersn

Answer 1

您需要使用REPLACE

函数REPLACE(yourcolumn, oldstring, newString)

使用

SET @variable = (SELECT REPLACE(yourcolumn, oldstring, newString) 
FROM yourTable WHERE something = something)

既然你没有提供很多细节，剩下的对你来说应该很容易。

因为我很无聊，我也想出了一个快速的方法来检测你应该使用哪个替换，以免在你的最终输出中得到重复的/n

if object_id('tempdb..#this') is not null drop table #this;
create table #this (col varchar(10), col2 varchar(10));
insert into #this VALUES ('rnrn','n'), ('rn','n');
declare @var varchar(125) = 'thisvaluernrn'

set @var = (SELECT REPLACE(@var, Col, col2) FROM (
SELECT t.col, col2, Row_NUmber() OVER (ORDER BY Len(col) desc) As RowNum 
FROM (select @var as Value) x CROSS JOIN #this t
WHERE   x.Value LIKE '%'+t.col+'%') y
WHERE RowNum=1)
select @var

基于你更新的需求，这变得很容易做:

SELECT replace(replace(@string, 'rn', 'n'), 'nn', 'n')

Answer 2

我会使用光标…你有一个这样的表

CREATE TABLE DBO.MAPPING (TO_BE_REPLACE VARCHAR(500), REPLACEMENT VARCHAR(500))
INSERT INTO DBO.MAPPING SELECT 'rnrn','n'
INSERT INTO DBO.MAPPING SELECT 'rn' ,'n'

，这将是程序

CREATE PROCEDURE DBO.SP_REPLACE
@TEXT VARCHAR(500)
AS
BEGIN
DECLARE 
@TO_BE_REPLACE VARCHAR(500), 
@REPLACEMENT   VARCHAR(500)
DECLARE CURSOR_REPLACE CURSOR
FOR SELECT 
TO_BE_REPLACE, 
REPLACEMENT
FROM 
DBO.MAPPING
OPEN CURSOR_REPLACE;
FETCH NEXT FROM CURSOR_REPLACE INTO 
@TO_BE_REPLACE, 
@REPLACEMENT
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TEXT = REPLACE(@TEXT,@TO_BE_REPLACE,@REPLACEMENT)
FETCH NEXT FROM CURSOR_REPLACE INTO 
@TO_BE_REPLACE, 
@REPLACEMENT
END;
CLOSE CURSOR_REPLACE;
DEALLOCATE CURSOR_REPLACE;
SELECT @TEXT
END

这个想法是运行这个

EXEC DBO.SP_REPLACE 'HELLOrn'

并获取'HELLOn'

这是你要找的吗?

edit:我将使用Doug的一些代码，这次只使用第一次出现的代码。

是的…还在用游标…对不起，Doug:)，这只是几条记录

CREATE TABLE DBO.MAPPING (TO_BE_REPLACE VARCHAR(500), REPLACEMENT 
VARCHAR(500), PRIORITY_ORDER INT)
INSERT INTO DBO.MAPPING SELECT 'rnrn','rn',1
INSERT INTO DBO.MAPPING SELECT 'rn' ,'n',2
go
CREATE PROCEDURE DBO.SP_REPLACE
@TEXT VARCHAR(500)
AS
BEGIN
DECLARE 
@TO_BE_REPLACE VARCHAR(500), 
@REPLACEMENT   VARCHAR(500)
DECLARE CURSOR_REPLACE CURSOR
FOR SELECT 
TO_BE_REPLACE, 
REPLACEMENT
FROM 
DBO.MAPPING
ORDER BY PRIORITY_ORDER ASC
OPEN CURSOR_REPLACE;
FETCH NEXT FROM CURSOR_REPLACE INTO 
@TO_BE_REPLACE, 
@REPLACEMENT
WHILE @@FETCH_STATUS = 0
BEGIN
--SET @TEXT = REPLACE(@TEXT,@TO_BE_REPLACE,@REPLACEMENT)
IF charindex(@TO_BE_REPLACE, @TEXT) <> 0
SET @TEXT =  stuff(@TEXT, charindex(@TO_BE_REPLACE, @TEXT), len(@TO_BE_REPLACE), @REPLACEMENT)
FETCH NEXT FROM CURSOR_REPLACE INTO 
@TO_BE_REPLACE, 
@REPLACEMENT
END;
CLOSE CURSOR_REPLACE;
DEALLOCATE CURSOR_REPLACE;
SELECT @TEXT
END

如果我们尝试

EXEC DBO.SP_REPLACE 'Hello!rnThis is for the testing 
ofrnrnStringreplacementrnwith charactersrnrnand special 
charactersrn'

输出将是

Hello!nThis is for the testing ofrnStringreplacementrnwith 
charactersrnrnand special charactersrn