Use SQL LIKE to find a number followed by space

Asked

Viewed 1,312 times

1

I have a field that gathers values that are sequences of numbers separated by space and comma, such as: "1 , 2 , 3 , 23 , 41 , 54"

I was using SQL inside PHP to find a number inside this field with LIKE, but the way I did, if I wanted to find only 5, for example, it would find 54 in the field I wrote up there, and that would be wrong.

$sql = mysql_query("select nm_linha from linhas where cidades_origem like '%$cod_cidades%' and ida like '%$cod_bairros%' and cidades_destino like '%$cod_cidades2%' and ida like '%$cod_bairros2%'");

cities_origin, going and destination cities are bank columns; $cod_cities, $cod_cities2, $cod_neighborhoods and $cod_bairros2 are PHP variables with the numbers I want to find.

How can I make the query to search the number in question preceded and followed by a space, and before and after these spaces may exist any content (%)?

php sql

  • In a perfect solution, you have to normalize this field in other tables.

2 answers

2


uses the separator comma to find ex:> LIKE '%, 5,%'... in this case the first record should be preceded by ", " also ex:>, 1, 2, 3, This I believe is the simplest method for your form of consultation...

2

For a cleaner solution, you can use the function FIND_IN_SET to search for something in a list, with no need to change it:

select
  nm_linha
from
  linhas
where
  find_in_set($cod_cidades, replace(cidades_origem, ' ', ''))
  and find_in_set($cod_bairros, replace(ida, ' ', ''))
  and find_in_set($cod_cidades2, replace(cidades_destino, ' ', ''))
  and find_in_set($cod_bairros2, replace(ida, ' ', ''))

Based on your example list, I used the REPLACE only to remove the spaces, but if you have the possibility of storing these values without spaces already eliminates this function from your query.

Here’s a SQL Fiddle for you to test the solution.

Browser other questions tagged php sql

You are not signed in. Login or sign up in order to post.