CODEWITHSUNDEEP
sql
tanu
tanu

Reputation: 65

Alternate To REPLACE

I am using REPLACE statement to replace certain string in my column.

update USER_DEFINED_DATA_SETS
set DefaultValue = replace(DefaultValue, 'Area', 'Area_123')

It works fine most of the times. But If I have Area and Area_Description data in my DefaultValue column, it replaces both to Area123 and Area_123_Description.

Area and Area_Description are different. I want to replace just Area and not Area_Description.

Sample Data

Sno     DefaultValue
1       Area
2       Area_Description
3       Area123
4       Equipment
5       Equipment_Name

Of all the above values only first row i.e DefaultValue=Area should change to Area123. Rest all should not change. Also i cant put a where clause like WHERE DeaultValue='Area' in my replace statement as I need this to work for all other scenarios like Equipment or any other.

Upvotes: 2

Views: 91

Answers (3)

André Kops
André Kops

Reputation: 2713

Just for the hell of it, I think this should work to replace only areas not flanked by letters, digits or underscores - change the pattern for other rules:

--Ends with
UPDATE USER_DEFINED_DATA_SETS SET DefaultValue = 
SUBSTRING(DefaultValue, 1, LEN(DefaultValue) - LEN('Area')) + 'Area123'
FROM USER_DEFINED_DATA_SETS
WHERE DefaultValue LIKE '%[^A-Za-z0-9_]Area'

--Starts with or equals
UPDATE USER_DEFINED_DATA_SETS SET DefaultValue = 
'Area123' + SUBSTRING(DefaultValue, LEN('Area') + 1, 9999999999)
FROM USER_DEFINED_DATA_SETS
WHERE DefaultValue LIKE 'Area[^A-Za-z0-9_]%'
OR DefaultValue = 'Area'

--In the middle - potentially multiple times
DECLARE @rowCount int SET @rowCount = 1
WHILE(@rowCount > 0)
BEGIN
UPDATE USER_DEFINED_DATA_SETS SET DefaultValue = 
    SUBSTRING(DefaultValue, 1, PATINDEX('%[^A-Za-z0-9_]Area[^A-Za-z0-9_]%', DefaultValue)) + 
    'Area123' + 
    SUBSTRING(DefaultValue, PATINDEX('%[^A-Za-z0-9_]Area[^A-Za-z0-9_]%', DefaultValue) + 1 + LEN('Area'), 9999999999)
FROM USER_DEFINED_DATA_SETS
WHERE DefaultValue LIKE  '%[^A-Za-z0-9_]Area[^A-Za-z0-9_]%'
SET @rowCount = @@ROWCOUNT
END

Upvotes: 0

Juan Diego Godoy Robles
Juan Diego Godoy Robles

Reputation: 14955

On oracle 11g and above.

SELECT REGEXP_REPLACE('Area Area_Description',
                      '(^|[^[:alnum:]_])Area($|[^[:alnum:]_])',
                      '\1Area_123\2') "Area"
FROM DUAL;

Results in:

Area
-------------------------
Area_123 Area_Description

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

You can add a WHERE condition to your UPDATE query to restrict the replacement to only occurrences where the DefaultValue column begins or ends with Area, or contains Area as a standalone word.

UPDATE USER_DEFINED_DATA_SETS
SET DefaultValue = REPLACE(DefaultValue, 'Area', 'Area_123') 
WHERE DefaultValue LIKE 'Area' OR
      DefaultValue LIKE 'Area ' OR
      DefaultValue LIKE ' Area ' OR
      DefaultValue LIKE ' Area'

Upvotes: 3

Related Questions