SQL - replace specific part of a string in multiple records

Question

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?

read about replace funtion. Please add the dbms tag which you are using — Jens
– Jens, Commented Jun 23, 2017 at 7:02

Tim Biegeleisen · Accepted Answer · 2017-06-23 07:02:42Z

10

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.

answered Jun 23, 2017 at 7:02

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Black Over a year ago

That worked thx! I wonder If this would also work with regular expressioins.

Tim Biegeleisen Over a year ago

If you're actually using MySQL, then the answer is no, because MySQL does not support regex replacement, at least not without writing your own user-defined function. You got lucky, perhaps, because normal replacements should cover your use case.

Mohd Aslam · Accepted Answer · 2017-06-23 07:09:37Z

1

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')

edited Jun 23, 2017 at 7:09

answered Jun 23, 2017 at 7:04

Mohd Aslam

662 bronze badges

Collectives™ on Stack Overflow

SQL - replace specific part of a string in multiple records

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related