CODEWITHSUNDEEP
mysqlsql
Black
Black

Reputation: 20382

SQL - replace specific part of a string in multiple records

Is it possible to replace a specific part of a string with another? E.g. I have a column which holds a path to a file, I renamed the folder download portal (on my physical harddrive) to download_portal, so I have to change the path in thousands of database records.

Example record before and after:

/fileadmin/download portal/test/myfile.jpg

after:

/fileadmin/download_portal/test/myfile.jpg

Let's assume this is my table fruits:

id | path
1  | /fileadmin/download portal/test/apple.jpg
2  | /fileadmin/download portal/test/banana.jpg
3  | /fileadmin/download portal/test/pineapple.jpg

How can I change download portal to download_portal in every record by using SQL? Can I solve it by using regular expressions?

Upvotes: 3

Views: 5898

Answers (2)

Mohd Aslam
Mohd Aslam

Reputation: 66

If you are using SSMS you can use REPLACE Function to get what you want.

Update tableName SET ColumnToChange = Replace(ColumnToChange, 'x_name','x_myname')

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

I don't think you necessarily need a regex replacement here. Just a normal replacement of download portal to download_portal should work. The following UPDATE should work on most databases:

UPDATE fruits
SET path = REPLACE(path, '/download portal/', '/download_portal/')
WHERE path LIKE '%/download portal/%'

Note: I think searching for /download portal/ is more restrictive, and safer, than just the plain text. This is so because it eliminates the chance of accidentally replacing download portal which appears as part of some other larger path name.

Upvotes: 10

Related Questions