Use SQL LIKE to find a number followed by space

Question

Use SQL LIKE to find a number followed by space

Navigation

#1 by (2 votes)
#2 by (2 votes)

1

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

I was using SQL within PHP to find a number within this field with LIKE, but the way I did it, if I wanted to find only 5, for example, it would find the 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%'");

source_city , ida and destination_city are bank columns; $ cod_cidades , $ cod_cidades2 , $ cod_buttons and $ cod_buttons are PHP variables with the numbers that I want to find.

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

php sql

asked by anonymous 24.04.2015 / 16:29

2 answers

2

For a cleaner solution, you can use the FIND_IN_SET to search for something in a list, without needing 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 list example, I used REPLACE only to remove the spaces, but if you have the ability to save those values already without spaces, remove this function from your query .

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

24.04.2015 / 17:08

How to remove a single element having several with the same id sass - how to compile the mined file?

score 2 · Accepted Answer

Use the comma-separator 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 inquiry form ...