Why am I getting an error when using the REPLACE function with a WHERE clause?

I am using SQL Server and SSMS.

I have the following code:

UPDATE Table 
SET [Code] = REPLACE(((REPLACE([CODE]), 'G1', 'G2') 
                      WHERE LEN([CODE]) = 2), 'C11', 'C33') 
WHERE LEN([CODE]) = 3

But I get the following error:

Msg 174, Level 15, State 1, Line 2
The replace function requires 3 argument(s).

I’d like to change G1 to G2 and C11 to C33. This is a small sample of a much larger code I’d like to build replacing many other similar codes.

Answer

You can use CASE for your “if” logic in SQL, so like this:

UPDATE Table 
SET [Code] = 
    CASE 
        WHEN LEN([Code]) = 2 THEN REPLACE([CODE],'G1','G2')
        WHEN LEN([Code]) = 3 THEN REPLACE([CODE],'C11','C33')
        ELSE [Code] -- lets be safe!
    END
WHERE
    LEN([Code]) IN (2,3) -- let be safe again!

You can use more conditions too:

UPDATE Correspondence 
SET [CPZ Code] = 
  CASE 
    WHEN LEN([CPZ Code]) = 1 AND [CPZ Code] = 'A' THEN 'A1'
    WHEN LEN([CPZ Code]) = 1 AND [CPZ_Code] = 'B' THEN 'A2'
    WHEN LEN([CPZ Code]) = 2 AND [CPZ_Code] = 'C1' THEN 'C1' -- not needed
    WHEN LEN([CPZ Code]) = 2 AND [CPZ Code] = 'C2' THEN 'C2' -- not needed
    WHEN LEN([CPZ Code]) = 2 AND [CPZ Code] = 'SW' THEN 'S1'
    ELSE [CPZ Code] -- lets be safe!
  END
WHERE
  [CPZ Code] IN ('A', 'B', 'C1', 'C2', 'SW') -- lets be safe again!

At this point it is starting to look like a lookup table would be nice though.

CREATE TABLE #temp(OldCode varchar(10), NewCode varchar(10))
INSERT INTO #temp 
VALUES
  ('A', 'A1'),
  ('B', 'A2'),
  ('C1', 'C1'),
  ('C2', 'C2'),
  ('SW', 'S1')
 
UPDATE t1
SET t1.[CPZ Code] = t2.NewCode
FROM Correspondence t1
INNER JOIN #temp t2
  ON t1.[CPZ Code] = t2.OldCode