CODEWITHSUNDEEP
sqlregexstringpostgresqlsubstring
Testudinate
Testudinate

Reputation: 25

How to delete character from list

I have row, example : 1,2,3,5,9,7 -> not in (3,7) (This character need to delete -> result select 1,2,5,9. How do it ?

For example : 

drop table test.table_4; 
create table test.table_4 (
    id integer, 
    list_id text
); 
insert into test.table_4 values(1,'1,2,3,5,9,7'); 
insert into test.table_4 values(2,'1,2,3,5'); 
insert into test.table_4 values(3,'7,9'); 
insert into test.table_4 values(5,'1,2'); 
insert into test.table_4 values(9,'1'); 
insert into test.table_4 values(7,'5,7,9');

query : 

select list_id from test.table_4 where id not in (3,7)  --return 4 row

    id    list_id
1.  1     '1,2,3,5,9,7'
2.  2     '1,2,3,5'
3.  5     '1,2'
4.  9     '1'

How to remove 3 and 7 in row 1 and 2 ?

    id
1.  1     '1,2,5,9'
2.  2     '1,2,5'
3.  5     '1,2'
4.  9     '1'

Upvotes: 0

Views: 109

Answers (3)

TobyLL
TobyLL

Reputation: 2306

The following should deal with 3 or 7 at the start of the string, at the end of the string, or anywhere in the middle. It also ensures that the 3 in 31 and the 7 in 17 don't get replaced:

select
   list_id,
   regexp_replace(list_id, '(^[37],|,[37](,)|,[37]$)', '\2', 'g')
from test.table_4
where id not in (3,7)

Explanation:
^[37], matches a 3 or 7 followed by a comma at the start of the string. This should be replaced with nothing.
,[37](,) matches a ,3, or ,7, in the middle of the string. This needs to be replaced with a single comma, which is captured by the brackets around it.
[37]$ matches a 3 or 7 preceded by a comma at the end of the string. This should be replaced with nothing.

\2 is used to replace the string - this is , for the second case above, and empty for cases 1 and 3.

Upvotes: 1

Houari
Houari

Reputation: 5651

You can use regexp_replace to get the expected output:

select  id, regexp_replace(list_id,'3,|,7', '','g') 
from table_4 
where id not in (3,7)

Output:

id  regexp_replace
1   1,2,5,9
2   1,2,5
5   1,2
9   1

Here is the SQL Fiddle

Upvotes: 0

Linger
Linger

Reputation: 15068

You could use the following statements to update all of the records. In the below example the first statement will remove any ,7 found. Then you execute the next statement to find any sting that has the 7 in the front of the string.

UPDATE test.table_4 SET list_id = REPLACE(list_id, ',7', '')
UPDATE test.table_4 SET list_id = REPLACE(list_id, '7', '')

If you also want to remove all occurrences of 3 then execute the following statements:

UPDATE test.table_4 SET list_id = REPLACE(list_id, ',3', '')
UPDATE test.table_4 SET list_id = REPLACE(list_id, '3', '')

However, it is a bad design to store values that you need to search agianst, work with, and etc in a string.

Upvotes: 0

Related Questions