Changing part of a string on some values in postgres database

Question

I have a table with three columns. Column1 is id. Column2 is filename. Column3 is location. I need to write a query that will take all files with filename = X and will replace part of the string for 'location' lets say location = '/home/AAA/bin' and it will replace it with '/home/BBB/bin'. The start of location is always the same but the end is not always the same. I mean it will always start with /home/AAA/ but the end of the location is different for every file - /bin/ could be /somethingelse/.

What did you try so far? What problems did you encounter?

Olaf
– Olaf

2012-06-06 18:17:36 +00:00
Commented Jun 6, 2012 at 18:17 — Olaf
– Olaf, Commented Jun 6, 2012 at 18:17

DaveShaw · Accepted Answer · 2012-06-06 21:53:10Z

1

You need to use this Postgres function

overlay(string placing string from int [for int]) 
ex: overlay('Txxxxas' placing 'hom' from 2 for 4)

Your situation involves the select statement having the following:

overlay(location placing '/home/BBB' from 1 for 9)

You can get more information from here.

edited Jun 6, 2012 at 21:53

DaveShaw

52.9k18 gold badges118 silver badges142 bronze badges

answered Jun 6, 2012 at 18:29

Mike

261 bronze badge

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

Collectives™ on Stack Overflow

Changing part of a string on some values in postgres database

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related