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

PostgreSQL – How can I check if string A contains number B using regex?

I want it to match only if the number B is not inside a bigger number.

e.g. I dont want it to match this: A = "12345" B = 234
I want it to match this: A = "94, 234, 88" B = 234

I tried looking at postgresql documentation but I am quite lost.

Tags:

2

Answers


  1. 0

    Storing CSV data in the format 94, 234, 88 directly in your table is probably poor table design. A better design would be to store each CSV number as a separate record, e.g.

    grp | val
1   | 94
1   | 234
1   | 88

    Then, to search for 234 you would only need a query like the following:

    SELECT *
FROM yourNewTable
WHERE val = 234;

    That being said, you could stick with your current design and use regular expressions to search for individual numbers:

    SELECT *
FROM yourTable
WHERE val_csv ~ 'y234y';
    Login or Signup to reply.
  2. 0

    Convert the column to an int[] then use the appropriate array operator:

    select ...
from ...
where B = any(('{' || A || '}')::int[])

    See live demo.

    Wrapping the string in { } is needed to create an array literal from a naked CSV.

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

Back To Top
Search