Page 1 of 1
Search all collumns at once in a database
Posted: Fri Oct 20, 2006 8:50 am
by Fangbeast
If I type "Select * from myTablename where myField = '" + WhatToLookFor.s
So that selects all data from myTablename where myField = the data.
Is there a way to state in SQL where I can state something like "SELECT * FROM myTablename WHERE ANY field = '" + WhatToLookFor.s
Instead of having to type:
"Select * from myTablename where
myField = '" + WhatToLookFor.s + ' OR
myField1 = '" + WhatToLookFor.s + ' OR
myField1 = '" + WhatToLookFor.s + ' OR
myField1 = '" + WhatToLookFor.s"
etc, etc
Posted: Fri Oct 20, 2006 11:43 am
by stubbsi
Nope. SQL requires that you specify the criteria against each column if that is what you want to search.
Is that a problem?
Based on the column names, you should be able to generate such an SQL statement by looking at the table layout, and therefore not require that you recode if you add columns.
If you want, i'll try and whack something together over the weekend.
depends on melbourne's weather though ...
Posted: Fri Oct 20, 2006 12:48 pm
by jqn
Well, you can type
"Select * from myTablename where concat(myField, myField1, myField2, ...) like '%WhatToLookFor%' "
(or "... contains 'WhatToLookFor' ", if permited in database syntax)
Joaquin
Posted: Fri Oct 20, 2006 2:54 pm
by Fangbeast
jqn wrote:Well, you can type
"Select * from myTablename where concat(myField, myField1, myField2, ...) like '%WhatToLookFor%' "
(or "... contains 'WhatToLookFor' ", if permited in database syntax)
Joaquin
jgn is "CONCAT( actually an sql keyword? I've never encounetered it before.
*EDIT* Never mind, I found it and wille xperiment. Thanks for the tip.
Edit. been testing this. Doesn't seem to work with sqlite. no results at all using the dll
Posted: Fri Oct 20, 2006 2:55 pm
by Fangbeast
stubbsi wrote:Nope. SQL requires that you specify the criteria against each column if that is what you want to search.
Is that a problem?
Based on the column names, you should be able to generate such an SQL statement by looking at the table layout, and therefore not require that you recode if you add columns.
If you want, i'll try and whack something together over the weekend.
depends on melbourne's weather though ...
Stubbsi, if I can work in Melbourne weather, then you can suffer with me damn it!!! My wife was just remarking tonight how frozen she was but I didn't feel it at all. We're up Boronia way at the foot of some hills.
Thanks for the offer but I have this one covered:):)
Posted: Sat Oct 21, 2006 12:18 am
by stubbsi
at least I'm near the beach - mind you, the wind we get can shake the house to its foundations.
its less to do with how cold, but how nice it might be -- and also how much my wife wants me to do around the house, and if the kids ...
well -- you know.
Posted: Sat Oct 21, 2006 6:33 am
by Fangbeast
stubbsi wrote:at least I'm near the beach - mind you, the wind we get can shake the house to its foundations.
its less to do with how cold, but how nice it might be -- and also how much my wife wants me to do around the house, and if the kids ...
well -- you know.
Stubbsi, did't I tell you not to get married and have kids?? Damn, I forgot. And I forgot to follow my own advice. I have one who is 23 and for all the input she gives here, I may as well do everything myself!!
Anyone want a 23 year old who does bugger all???? I'm giving it away, no return policy.
Posted: Sat Oct 21, 2006 10:51 am
by stubbsi
I have a 16 year old and a 13 year old.
Boys.
13 year old severe teenager, 16 year old still the old man a cuddle.
Posted: Sat Oct 21, 2006 12:39 pm
by Fangbeast
Cuddle? What's that. I have to whinge mine into even thinking about it.
@stibbsi and @jqn
Posted: Tue Feb 20, 2007 3:31 am
by Fangbeast
I found some help in an sqlite forum (yippee!!) to solve this problem.
Query.s = "SELECT * FROM addresses WHERE "
Query.s + "title||first||middle||last||street||suburb||city||postcode||state||country||work||home||mobile||"
Query.s + "company||misc1||misc2||email||note||category||displayas||firstentry||lastedit||deleted "
Query.s + "LIKE '%" + SearchText.s + "%' ORDER BY displayas"
will do field concatenation in sqlite (maybe even in other sql variants"
Posted: Tue Feb 20, 2007 12:23 pm
by GedB
Fangbeast,
I think your best bet is to write a procedure that will generate the sql.
Concatng the strings together isn't a good idea, because it means you lose the benefits of any indexes.
Posted: Tue Feb 20, 2007 12:59 pm
by Fangbeast
GedB wrote:Fangbeast,
I think your best bet is to write a procedure that will generate the sql.
Concatng the strings together isn't a good idea, because it means you lose the benefits of any indexes.
GedB, that only works when you know what you are doing (ROFL), and I don't. There are no indexes in this database and may never need to be with any luck:):)