Search all collumns at once in a database

[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

[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 ...

[jqn]

Well, you can type
"Select * from myTablename where concat(myField, myField1, myField2, ...) like '%WhatToLookFor%' "
(or "... contains 'WhatToLookFor' ", if permited in database syntax)

Joaquin

[Fangbeast]

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

[Fangbeast]

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:):)

[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.

[Fangbeast]

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.

[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.

[Fangbeast]

Cuddle? What's that. I have to whinge mine into even thinking about it.

[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"

[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.

[Fangbeast]

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:):)