SQL question

[Trond Hoyem]

Hi

I am doing some quries to the database to be able to find some objects depending on different values. I have been able to find it, but I want some help to understand why things are as they are.

The code I was trying to use was:

Code:

newNavn = db:getall('SELECT name FROM objects WHERE name LIKE ? AND name LIKE ?', origObjKode, term )

This returned an empty table. 

I then changed the code to 

Code:

newNavn = db:getall('SELECT name FROM objects WHERE name LIKE "%'.. origObjKode ..'%" AND name LIKE "%'.. term ..'%"')

This returned a table with the information I wanted. 

I have used the structure from the first example before, and it has worked, but now it did not. Why is it so? 

I am sure I have done something wrong in the first example, I just do not understand what it is.

[admin]

LIKE "something" is an exact match but not case-sensitive. LIKE "%something%" is a partial match which looks for values that contain "something".

[Trond Hoyem]

LIKE "something" is an exact match but not case-sensitive. LIKE "%something%" is a partial match which looks for values that contain "something".

OK, but still I do not see it.
If I use the code 

Code:

elementer = db:getall('SELECT name FROM visobjects WHERE floor LIKE ? AND type NOT LIKE ?', origID, 6)

I will get a result. In this I am also looking for objec(s) containing "something", am I not? The values in the variables in my first post are the same in both examples.

[admin]

For numeric values you should use = and !=, LIKE is meant for strings. See this: https://stackoverflow.com/questions/5435...ls-vs-like

[Trond Hoyem]

For numeric values you should use = and !=, LIKE is meant for strings. See this: https://stackoverflow.com/questions/5435...ls-vs-like

But in the examples in my forst post, both variables contain strings, there is no numbers there.
In the last example I used LIKE and found numeric values.

Sorry for beeing slow, but I really do not understand why the first code I used did not work.

[admin]

You have "WHERE name LIKE ? AND name LIKE ?" which will only find something when both parameters are the same and there is an object with exactly the same name as both parameters.

When you use % wildcard on both sides it find objects where name contains parameter A and contains parameter B. For example it can find object named "aabbcc" when the first parameter is "aa" and the second one is "bb".