Give preference to greater equality, mysql

Navigation
1

I have a table with a column named X in it I enter comma-separated values, for example: 1,2,3,4,5 .

I use this query to get a particular row.

SELECT * FROM 'x' WHERE CONCAT(',',x,',') LIKE '%,1,2,%'

I wanted to adapt this query to give preference to rows that have the least difference. for example, I have rows this way:

1,2,3,4,5,6,8,9 [..]

1,2,3,8,91

3,4,5,1

1.2

When I play the query it shows me all rows, because they all contain 1,2 , so I wanted it to give preference to rows whose difference is smaller or it would show me in that order:

1,2,3,4,5,6,8,9 [..] - Third

1,2,3,8,91 - Second

3,4,5,1 - Room

1.2 - First

    
asked by anonymous 03.01.2015 / 18:12

1 answer

1
  

Note: Depending on the amount of records and the size of the column, the query performance will probably not be good.

One way to show the smallest difference is by considering the number of characters of the returned rows, the smaller the number of characters, the smaller the difference of the search term. To do this you can use the CHAR_LENGTH function as the example below: 

select 
  [SuasColunas]
from 
  [NomeDaTabela] 
where 
  [NomeDaColuna] like '%1,2%' 
order by 
  char_length([NomeDaColuna])

You can see it working in SQL Fiddle .

    
03.01.2015 / 19:22
Query number of MySQL returns Ignore \ n no with bash