Ashish Rajput Ashish Rajput - 5 months ago 23
SQL Question

Update Some part of a Column based on matched pattern

I have a configuration table where application url get stored. Application is on various server and everytime when we need to setup a new server, we need to update the values by selecting and running a while loop in sql. Scenerios is like

Table : Configuration
Values

Id | key | DefaultUrl

1 | Key1 | www.xxx.com/something1

2 | key2 | www.xxx.com/something2

3 | key3 | www.xxx.com/something3


There are many more keys on this pattern and some other table too. As this is a legacy software i can not change the structure.

Whenever i need to setup a new server then i need to replace the url part from
www.xxx.com
to
www.yyy.com
. To achieve this i do something like


  • Write a select query on like pattern

  • Loop through the data and replace the data in every row one by one.



So right now i am looking a better approach to achieve the same. Please help!

Answer

The only possible problem is when www.xxx.com is somewhere in the middle of the url and it shouldn't be replaced. Use some forbidden in url character ' ' for example.

UPDATE [Configuration Values]
SET DefaultUrl = replace(' '+DefaultUrl, ' www.xxx.com', 'www.yyy.com')
WHERE DefaultUrl LIKE 'www.xxx.com%'
Comments