Ashish Rajput Ashish Rajput - 1 year ago 90
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

Id | key | DefaultUrl

1 | Key1 |

2 | key2 |

3 | key3 |

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
. 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 Source

The only possible problem is when 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, '', '')
WHERE DefaultUrl LIKE ''