SQL Server Replace

user_10327350 0
Viewed 75
CREATE TABLE test (a VARCHAR(2000))

INSERT INTO test 
VALUES ('This is a AB_CD_test.dbo.ABC'), 
       ('This is a AB_CD.dbo.ABC')

Table before running Update query:

This is a AB_CD_test.dbo.ABC
This is a AB_CD.dbo.ABC
This is a [AB_CD].[dbo].[XYZ]

I am trying to replace the keyword AB_CD with AB_CD_test using this query:

UPDATE [US\AF83767].[test]
SET a = REPLACE(a, 'AB_CD', 'AB_CD_test')
WHERE a LIKE '%AB!_CD%' ESCAPE '!';

But as expected, data changes to:

This is a AB_CD_test_test.dbo.ABC
This is a AB_CD_test.dbo.ABC
This is a [AB_CD_test].[dbo].[XYZ]

The second is fine but the first should remain 'This is a AB_CD_test.dbo.ABC'.

Could anyone help with a query which does not change the first row and just looks for occurrences of 'AB_CD'.

Relatively new to SQL and would appreciate your help.

sql-server sql
3 Answers

It's not exactly clear what you want to prevent. Do you only want to prevent the replacement when "Test" is present, or any string between "AB_CD" and ".dbo"

I'm guessing the latter, so I would suggest this:

UPDATE [US\AF83767].[test]
SET a = REPLACE(a, 'AB_CD', 'AB_CD_test')
WHERE a LIKE '%AB!_CD.%' ESCAPE '!';
user_15075660

It appears that you want to replace instances of AB_CD ONLY WHEN it is followed by a period or a closing bracket or nothing at all.

where col like '%AB!_CD[.!]]%' ESCAPE '!' or col like '%AB!_CD' ESCAPE '!';
user_94580720

I think this will do what you want:

UPDATE #test
SET a = REPLACE(REPLACE(a, 'AB_CD_test', 'AB_CD'), 'AB_CD', 'AB_CD_test')
WHERE a LIKE '%AB!_CD%' ESCAPE '!';

It replaces all occurrences of AB_CD_test to AB_CD... and then changes all AB_CD to AB_CD_test.

user_47652000
Related