This forum uses cookies
This forum makes use of cookies to store your login information if you are registered, and your last visit if you are not. Cookies are small text documents stored on your computer; the cookies set by this forum can only be used on this website and pose no security risk. Cookies on this forum also track the specific topics you have read and when you last read them. Please confirm that you accept these cookies being set.

SQL question
#1
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.
There are 10 kinds of people in the world; those who can read binary and those who don't  Cool
Reply
#2
LIKE "something" is an exact match but not case-sensitive. LIKE "%something%" is a partial match which looks for values that contain "something".
Reply
#3
(05.02.2021, 07:46)admin Wrote: 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.
There are 10 kinds of people in the world; those who can read binary and those who don't  Cool
Reply
#4
For numeric values you should use = and !=, LIKE is meant for strings. See this: https://stackoverflow.com/questions/5435...ls-vs-like
Reply
#5
(05.02.2021, 12:57)admin Wrote: 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.
There are 10 kinds of people in the world; those who can read binary and those who don't  Cool
Reply
#6
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".
Reply


Forum Jump: