+- Logic Machine Forum (https://forum.logicmachine.net)
+-- Forum: LogicMachine eco-system (https://forum.logicmachine.net/forumdisplay.php?fid=1)
+--- Forum: Scripting (https://forum.logicmachine.net/forumdisplay.php?fid=8)
+--- Thread: SQL question (/showthread.php?tid=3147)
SQL question - Trond Hoyem - 05.02.2021
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 ..'%"')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.
RE: SQL question - admin - 05.02.2021
LIKE "something" is an exact match but not case-sensitive. LIKE "%something%" is a partial match which looks for values that contain "something".
RE: SQL question - Trond Hoyem - 05.02.2021
(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.
RE: SQL question - admin - 05.02.2021
For numeric values you should use = and !=, LIKE is meant for strings. See this: https://stackoverflow.com/questions/543580/equals-vs-like
RE: SQL question - Trond Hoyem - 05.02.2021
(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/543580/equals-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.
RE: SQL question - admin - 05.02.2021
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".