SQL Find & Replace and ignore duplicate replacements

Question

I have a database in SQL with a list of IDs and I want to replace IDs that have their last digit as a 4 or 5 (excluding those that end in "04", "05", "14", or "15") and replace it with 14 or 15. The problem is that the following command comes up with a duplication error:

  SET ID = REPLACE(ID, *'X4', *'X14');

For example, consider the following ID data:

BOA04
SBA04
SBH4
BOH4
BOH14
BOZ4

Hence in the above data I only want to change SBH4 to SBH14 and BOH4 to BOH14 (but this is where it will be duplicated) and BOZ4 to BOZ14.

To summarise: For all IDs that are 4 characters in length, if the last digit is 4, then replace the last digit with 14, else if the last digit is 5, replace with 15.

If this entry already exists then ignore the replace.

Thanks in advance.

Try UPDATE IGNORE ... (to ignore the duplicate errors) but it seems a little weird to me. If I were you I would take a good long look at what you're trying to do and see if there isn't a clearer route forward. — Peter Bowers
– Peter Bowers, Commented Feb 25, 2015 at 9:48
Add a "NOT EXISTS (sub-select)" to the where clause, to verify no 14/15 row already exists. — jarlh
– jarlh, Commented Feb 25, 2015 at 9:50

jpw · Accepted Answer · 2015-02-25 10:16:31Z

1

This should do it:

update your_table
set id = stuff(id, 4, 0, '1')
from your_table where right(id, 1) in (4,5)
and len(id) = 4
and stuff(id, 4, 0, '1') not in (select id from your_table)

The last part: and stuff(id, 4, 0, '1') not in (select id from your_table) makes sure you won't have any duplicates.

The right(id, 1) in (4,5) and len(id) = 4 could be replace with id like '__[^01][45]' as in t-clausen.dks answer.

edited Feb 25, 2015 at 10:16

answered Feb 25, 2015 at 10:06

jpw

45k6 gold badges71 silver badges90 bronze badges

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

3 Comments

Du_ Over a year ago

Hi Thanks JPW, However I am getting (0 row(s) affected) when I run the above?

jpw Over a year ago

@Du_ Oh ok, I guess the not in predicate might exclude too much maybe; try doing a select * from your_table where stuff(id, 4, 0, '1') not in (select id from your_table) query to see if it returns any rows.

Du_ Over a year ago

Hi JPW thanks for the quick reply. It's strange, the above returns some values like [ABF5, ABM5, ABN14] but doesn't include values such as [ABG5, ABH14, ABH4 etc]

t-clausen.dk · Accepted Answer · 2015-02-25 10:20:06Z

1

Solution for sql-server:

This will select the values

SELECT CASE WHEN x like '__[^01][45]' THEN STUFF(x, 4, 0, '1') ELSE x END
FROM 
(values('BOA04'),('SBA04'),('SBH4'),('BOH4'),('BOH14'),('BOZ4')) x(x)

This will update a table:

DECLARE @t table(x varchar(5))
INSERT @t
values('BOA04'),('SBA04'),('SBH4'),('BOH4'),('BOH14'),('BOZ4')

UPDATE @t SET x = STUFF(x, 4, 0, '1')
FROM @t
WHERE x like '__[^01][45]'

edited Feb 25, 2015 at 10:20

answered Feb 25, 2015 at 9:56

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

1 Comment

jpw Over a year ago

I upvoted this before I realized that it doesn't produce the desired outcome; I think you want STUFF(x, 4, 0, '1') instead, but then you'll get the duplicates the OP wants to avoid so a where clause that excludes potential updates that would produce duplicates is needed too.

Collectives™ on Stack Overflow

SQL Find & Replace and ignore duplicate replacements

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related