frenciaj frenciaj - 3 months ago 7
SQL Question

SQL LIKE search error only on one row of table

This is weird, it never happens to me before

Im on the rails console, because my search form is not working, for some reason when i do a search using the row called 'nombre', the search doesnt work, this is the output data:

First, i will show you my table data:

2.3.0 :035 > Item.all
Item Load (0.6ms) SELECT "items".* FROM "items"
=> #<ActiveRecord::Relation [#<Item id: 1, nombre: "Melamina Blanca", espesor: 18, material: "MDF", quantity: 14, created_at: "2016-08-04 00:56:13", updated_at: "2016-08-30 00:05:01">, #<Item id: 2, nombre: "Melamina Gris Grafito", espesor: 15, material: "Aglo", quantity: 9, created_at: "2016-08-04 00:56:13", updated_at: "2016-08-30 00:05:01">, #<Item id: 3, nombre: "Melamina Azul", espesor: 18, material: "MDF", quantity: 26, created_at: "2016-08-04 00:56:13", updated_at: "2016-08-30 00:05:01">, #<Item id: 4, nombre: "Melamina Rojs", espesor: 18, material: "MDF", quantity: 5, created_at: "2016-08-04 00:56:13", updated_at: "2016-08-30 00:05:01">]>
2.3.0 :036 >


So as you can see, the first item, the name (nombre) is 'Melamina Blanca'

If i do the search, i get the following:

item = Item.where("nombre LIKE ?", "Blanca")
Item Load (0.8ms) SELECT "items".* FROM "items" WHERE (nombre LIKE 'Blanca')
=> #<ActiveRecord::Relation []>


So i tried to search by another row, on this case called 'Material'

item = Item.where("material LIKE ?", "Aglo")
Item Load (0.5ms) SELECT "items".* FROM "items" WHERE (material LIKE 'Aglo')
=> #<ActiveRecord::Relation [#<Item id: 2, nombre: "Melamina Gris Grafito", espesor: 15, material: "Aglo", quantity: 9, created_at: "2016-08-04 00:56:13", updated_at: "2016-08-30 00:05:01">]>


I get a correct search, i tried with the other rows and all of them work, except the one i need, which is 'nombre'

Any ideas why?

Answer

I think the problem is you are searching for an exact match. To search for a pattern string, you have to use % which defines wildcards.

Try

Item.where("nombre LIKE ?", "%Blanca%")

This will look for records with names that contain "Blanca" in it.

If you want to search for names that start with "Blanca", use

Item.where("nombre LIKE ?", "Blanca%")

Or for names ending with "Blanca", use

Item.where("nombre LIKE ?", "%Blanca")

Note: Your search across material "Aglo" was successful because the material field in the record contained the exact string "Aglo".

Hope this helps!

Comments