CODEWITHSUNDEEP
sqlpostgresql
Garine
Garine

Reputation: 604

find character within a string sql

I have a string column called day with indexes of the days of week as follows

1,2,3,4,5,6,7

I want to check if for example 1 exists in the string within the column.

the logic should be like this.

select * from table where 1 is in day;

how can I achieve this with psql?

Upvotes: 0

Views: 90

Answers (3)

user330315
user330315

Reputation:

You shouldn't be storing comma separated values in the first place.

The solution using like or position() can fail if you have two-digit numbers in those values, e.g. '12,13' would be found by position('1' in day) as well.

To avoid that, convert the de-normalized value into an array, then search in the array:

select * 
from the_table
where '1' = any(string_to_array(day, ','));

Online example: https://rextester.com/GEMN57014

Upvotes: 2

D-Shih
D-Shih

Reputation: 46249

Another way You can try to use like

PostgreSQL 9.6 Schema Setup:

CREATE TABLE T(col1 varchar(50));

INSERT INTO T VALUES ('1,2,3,4,5,6,7');
INSERT INTO T VALUES ('2,3,4,5,6,7,1');
INSERT INTO T VALUES ('5,6,7');

Query 1:

select *
from T
WHERE col1 like concat('%','1',',%') or  col1 like concat('%,','1','%')

Results:

|          col1 |
|---------------|
| 1,2,3,4,5,6,7 |
| 2,3,4,5,6,7,1 |

Upvotes: 1

jarlh
jarlh

Reputation: 44795

select * from table
where position('1' in day) > 0

But in general it's a bad idea to store comma separated items in a column, it will only cause you lots of trouble.

Upvotes: 2

Related Questions