Search PostgreSQL column for substring

Question

I have a DB column that has entries like this:

I want to search for the rows that match a certain number - for example "45" would should return the first and third rows but not the second.

Tim Biegeleisen · Accepted Answer · 2019-09-11 16:22:59Z

4

We can try using a regex approach here with word boundaries:

select col
from your_table
where col ~* '\y45\y';

Demo

answered Sep 11, 2019 at 16:22

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

user330315 · Accepted Answer · 2019-09-11 21:06:10Z

3

You can convert the delimited string to an array and then test the array

select *
from the_table
where '45' = any(string_to_array(the_column, '/'))

answered Sep 11, 2019 at 21:06

user330315