Question 260543 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to remove the current character in a Postgres string

I need to remove only character 1 from the string ‘[1,11, 1, 111,12,1,11]’ in Postgres without affecting the other characters. Symbol 11 and 111, 12 should not be affected. I can’t cope with this task. Please help me

The end result should be the string ‘[11,111,12,11]’

I tried different replace options REPLACE(%s,’ 1,’,”), REPLACE(%s,’1 ,’,”)

Tags:

2

Answers


  1. 0

    Use substring(string [from <str_pos>] [for <ext_char>]).

    UPDATE table_name SET field_name = SUBSTRING(field_name, 2) WHERE ...
    Login or Signup to reply.
  2. 0

    Inside out: first replace [ and ] with { and } for correct array syntax, then cast the resulting string to a text array and unnest it into a table of strings. Select elements that are not equal to ‘1’, aggregate the resultset back into a string and format it within square brackets.

    select format('[%s]', string_agg(e, ','))
from unnest(replace(replace('[1,11, 1, 111,12,1,11]','[','{'),']','}')::text[]) e
where not e = '1';

    DB-Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search