How to Replace a character in a string which get as an input parameter using SQL stored procedures-CodePudding

I have a table with two columns, one is for storing characters to be replaced and other with the replacement character.

Let rows of my table(each column is separated with '|') are

\r\n\r\n | \n
\r\n | \n

How can I write a stored procedure which accepts string as parameter and replace the characters of the string with these column values. (Like if a string which contains \r\n then it must be replaced by \n)

CodePudding user response：

Yolu want to use the function REPLACE

REPLACE(yourcolumn, oldstring, newString)

Used like

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

Since you didnt provide alot of details, figuring out the rest should be pretty easy for you.

Because I was bored I also came up with a quick way to detect which replacement you should use as to not get duplicate /n in you final output

if object_id('tempdb..#this') is not null drop table #this;

create table #this (col varchar(10), col2 varchar(10));

insert into #this VALUES ('\r\n\r\n','\n'), ('\r\n','\n');

declare @var varchar(125) = 'thisvalue\r\n\r\n\'


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

CodePudding user response：

I would use a cursor... you have a table like this

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

and this would be the procedure

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

the idea is to run this

EXEC DBO.SP_REPLACE 'HELLO\r\n'

and get 'HELLO\n'

is this what you are looking for?