Replacing strings in SQL Server

Question

I have a function below and also the output, it is basically to find particular letters in a string and replace them with some other letters, for example in this string abcde I want to replace de with xy, so I give @input = abcde, @find = de and @replace = xy, so I get the output as abcxy, but if assume I have a string hat and I want to replace h and t with m and c, how do i achieve this?

And I must find the solution without using a while loop in my function

create  FUNCTION dbo.replace_letters
(
  @Input AS VarChar(1000),
  @Find AS VarChar(100),
  @Replace AS VarChar(100)
)
RETURNS VarChar(1000)
AS
BEGIN
   SELECT @Input = REPLACE(@Input, @Find,@Replace)

   return @input
end

--run
select dbo.replace_letters ('abcde', 'ed', 'dc')

Why do you need a function? why not directly use the replace function? what is the problem you can not solve? it sounds it work and looks good? why not just call the replace function for all replacements you need to perform? — CloudyMarble
– CloudyMarble, Commented Aug 3, 2012 at 5:27

marc_s · Accepted Answer · 2012-08-03 05:35:44Z

3

The easiest way is, to run the function a second time :-)

select dbo.replace_letters(dbo.replace_letters('hat', 'h', 'm'), 't', 'c')

edited Aug 3, 2012 at 5:35

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 3, 2012 at 5:29

Tobi

1,4381 gold badge13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

G.S · Accepted Answer · 2012-08-03 07:00:23Z

1

Simply run

select REPLACE(REPLACE ('HAT','H','M'),'T','C').

SQL Server provides the replace function then why to write your own for doing same task ?

answered Aug 3, 2012 at 7:00

G.S

7772 gold badges11 silver badges22 bronze badges

Comments

Mohit Pandey · Accepted Answer · 2012-08-03 05:47:41Z

0

Try this: It will replace first character from @Find and replace it with first character of @Replace and second character from @Find and replace it with second character of @Replace.

CREATE  FUNCTION dbo.replace_letters
(
  @Input AS VARCHAR(1000),
  @Find AS VARCHAR(100),
  @Replace AS VARCHAR(100)
)
RETURNS VARCHAR(1000)
AS
BEGIN
   SELECT @Input = REPLACE(REPLACE(@Input, SUBSTRING(@Find,1,1),SUBSTRING(@Replace,1,1)),SUBSTRING(@Find,2,1),SUBSTRING(@Replace,2,1))
   RETURN @input
END

 SELECT dbo.replace_letters('hat','ht','mc')
 SELECT dbo.replace_letters('hahtaahtat','ht','xy')

ANSWER IS :

mac

xaxyaaxyay

answered Aug 3, 2012 at 5:47

Mohit Pandey

3,8597 gold badges28 silver badges38 bronze badges

Collectives™ on Stack Overflow

Replacing strings in SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related