Hi I need to add WHERE clause to my query. The problem is that the number column contains value like:
(row 1) 1015/14,910/15
(row 2) 90/15,14/15
(row 3) 10/15, 900/15, 1000/15
(row 4) 1019/15, 1018/15
(row 5) null
....
It's number + year. In the column I can have more numbers or null value. Column type: string.
Now I need to find number "10/15" or other.
I tried using number LIKE '%10/15%' but result is: (row1) 910/15
AND (row3) 10/15...
so I can't use it.
When I try use WHERE nr = '10/15' I find only row with 1 number = 10/15.
So how do I find the rows only with my part of string but only full number? not part?
I think a regular expression might work nicely here:
where num_text ~ '\D10/15\D|^10/15\D|\D10/15$|^10/15$'
the \D atom indicates any non-digit.
This is a little more obscure, but a ts_vector might actually work for you:
select *
from Your_Table
where
to_tsvector(num_text) @@ '10/15'
The ts_vector approach as a scalability advantage to it -- if you are searching millions of rows, there are ways to make this really fast. If you have 1,000 rows, then it hardly matters.