Search code examples
sqlpostgresqlreplacesql-update

Multiple Replace conditions in Postgres

I have a column named "Path" in a table called Purchase

\\fs01dsc.test.com\data\products\
\\ks01dsc.test.com\items\books\

I need to update the domain name for all values in this column like fs01dsc.test.com to xyz.com and also need to change the \\ to / and \ to /.

So the expected output is

/xyz.com/data/products/
/xyz.com/Items/books/

Tried the below queries to update but it seems there would be a better apporoach that I'm not sure about

UPDATE Purchase
SET "PATH" =  LOWER(REPLACE("PATH", '\','/')) 

UPDATE Purchase
SET "PATH" = REPLACE("PATH", split_part("PATH" , '/', 3), 'xyz.com')
Solution

  • You can nest your replace calls:

    UPDATE Purchase
SET    "PATH" = 
       LOWER(REPLACE(REPLACE("PATH", split_part("PATH" , '/', 3), 'xyz.com'), '\', '/')