Truncating and updating string values in database table

Question

I have a postgres db with a table named data. as an example this table contains 6 entires where 3 are erroneous:

data
-----------------
foo/bar/file1.txt
foo/bar/file2.txt
file3.txt
foo/bar/file4.txt
file5.txt
file6.txt

I am looking for a way to strip the 3 files with extra path such that the output becomes:

data
-----------------
file1.txt
file2.txt
file3.txt
file4.txt
file5.txt
file6.txt

SUBSTRING(file_name, 50,70) I can select the right result, but I cant exactly figure out how I rename them in my table: SELECT substring(data, 9, 100) FROM db WHERE data LIKE 'foo/bar%';

Do you want to change the data in the table? Or just display it differently? — user330315
– user330315, Commented Feb 20, 2019 at 10:07

Kaushik Nayak · Accepted Answer · 2019-02-20 10:11:03Z

3

You probably want an UPDATE. Also, better use SUBSTRING(file_name from 'foo/bar/(.*)$') instead of relying on character length. This can help you to reuse the query just by changing the pattern and not counting the length.

UPDATE data SET file_name =  SUBSTRING(file_name from 'foo/bar/(.*)$')
where file_name LIKE 'foo/bar%';

Demo

answered Feb 20, 2019 at 10:11

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Comments

user330315 · Accepted Answer · 2019-02-20 10:17:59Z

2

You can convert the value into an array and then pick the last entry of that array. That way you don't need to hardcode the file paths in the update statement:

update the_table 
   set data = (string_to_array(data, '/'))[cardinality(string_to_array(data, '/'))]
where strpos(file_name , '/') > 0

answered Feb 20, 2019 at 10:17

user330315

Comments

pirho · Accepted Answer · 2019-02-20 10:35:13Z

1

This is one one way to get the last item after /:

UPDATE *my_table* SET data =
    (string_to_array(data, '/'))[array_length(string_to_array(data, '/'), 1)];

So convert to array the second convert is to get the size of that array to have the last index.

edited Feb 20, 2019 at 10:35

answered Feb 20, 2019 at 10:17

pirho

12.4k13 gold badges57 silver badges77 bronze badges

Collectives™ on Stack Overflow

Truncating and updating string values in database table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related