I have a field (of type character varying) called 'directedlink_href' in a table which contains arrays that have values that all start with a '#' character.
How am I able to remove the '#' character from any entries in these arrays in this field?
For instance...
{#osgb4000000030451486,#osgb4000000030451491}
to
{osgb4000000030451486,osgb4000000030451491}
The clean solution is to unnest, replace and then re-aggregate the values:
select id,
(select array_agg(substr(x.val,2) order by x.idx) from unnest(t1.directedlink_href) with ordinality as x(val,idx)) as data
from the_table t1;
If you want to actually change the data in the table:
update the_table t1
set directedlink_href = (select array_agg(substr(x.val,2) order by x.idx) from unnest(t1.directedlink_href) with ordinality as x(val,idx));
This simply strips off the first character. If you might have other characters at the start of the value, you need to use regexp_replace(x.val,'^#', '') instead of the substr(x.val,2)
@a_horse_with_no_name got my upvote for a cleaner and more "Posgres-ish" solution.
I was about to delete this answer, but after some tests, it seems that performance wise this solution has an advantage.
Therefore, I would leave this solution here, but I do recommend to choose the solution of @a_horse_with_no_name as the right answer.
I'm using chr(1) has a character that most likely does not appear in the array's' elements.
select string_to_array(substr(replace(array_to_string(directedlink_href,chr(1)),chr(1)||'#',chr(1)),2),chr(1))
from t
;
Think this is a simpler and more generic solution, thought I'd share:
SELECT regexp_split_to_array(regexp_replace(array_to_string(ARRAY['#osgb4000000030451486','#osgb4000000030451491'], '__DELIMITER__'), '#', '', 'g'), '__DELIMITER__');
