CODEWITHSUNDEEP
sqlsql-server
Abhi
Abhi

Reputation: 89

SQL Server Replace

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.

Upvotes: 4

Views: 88

Answers (3)

J Sidhu
J Sidhu

Reputation: 677

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 '!';

Upvotes: 1

Brian Pressler
Brian Pressler

Reputation: 6713

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.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

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 '!';

Upvotes: 2

Related Questions