Query to find if value is between "1 | 3"

Question

Query to find if value is between "1 | 3"

Navigation

#1 by (4 votes)

5

I have a unit table that represents the number of bedrooms of the building:

id|  dorm
1 |   1|3

In this case above it means that the property id 1 has units with 1 or 3 bedrooms.

Then I have a search using $_GET where the dorm field is dorm = 2, for example

In this example I need the query to bring this id 1, because the person selected at least 2 dormitories, and as in the column dorm has 1 | 3 (that means that it has units of 1 or 3 dormitories) it must bring id 1.

To do the query I would have to "break" the dorm column, this? I do not know how

mysql select

asked by anonymous 10.05.2018 / 16:29

1 answer

Error converting varchar value to integer Only positive numbers in sum aggregation

score 4 · Accepted Answer

Use the function SUBSTRING_INDEX by separating the values and comparing them with the value received in GET.

The third positive parameter will return the left value of the | delimiter, and negative will return the right value of the delimiter :

"SELECT id FROM tabela
WHERE SUBSTRING_INDEX(dorm,'|',1) >= '$dorm'
OR
SUBSTRING_INDEX(dorm,'|',-1) >= '$dorm'"

If a or other value is equal to or greater than the GET value, it will return the record.