Select statement with multiple 'where' fields using same value without duplicating text

Question

I will start by saying that I don't think what I want can be done, but that said, I am hoping I am wrong and someone knows more than me. So here is your chance... Prove you are smarter than me :)

I want to do a search against a SQLite table looking for any records that "are similar" without having to write out the query in long hand.

To clarify this is how I know I can write the query:

select * from Articles where title like '%Bla%' or category like '%Bla%' or post like '%Bla%'

This works and is not a huge deal if you are only checking against a couple of columns, but if you need to check against a bunch then your query can get really long and nasty looking really fast, not to mention the chance for typos. (ie: 'Bla%' instead of '%Bla%')

What I am wondering is if there is a short hand way to do this?

*This next code does not work the way I want, but just shows kind of what I am looking for

select * from Articles where title or category or post like '%Bla%'

Anyone know if there is a way to specify that multiple 'where' columns should use the same search value without listing that same search value for every column?

Thanks in advance!

Answer 1

I think considering you are using sqlite you should consider using Full Text Search. It'll make what you're attempting to do more efficient.

http://www.sqlite.org/cvstrac/wiki?p=FtsUsage

Answer 2

The quickest thing I can think of, is to concatenate title, category and post, and see if the concatenated string matches '%Bla%'. To make this work reliably, use a separator like ',' when concatenating (else, you could have title 'Bl' and cagegory 'a').

I don't know off the top of my head, what the concatenation operator is called in SQL, and I'm not sure, if one exists in generic SQL or SQLite.

My idea is, that it could look something like:

select * from Articles where concat(title, ',', category, ',', post) like '%Bla%'

Answer 3

Maybe you want to concatenate your fields into a single string and then LIKE against that?

select * from Articles where (title || ';' || category || ';' || post) like '%Bla%'

The semicolons in between are arbitrary and are there to ensure that "ROB" and "lance" don't bring back a false positive.

If you want to not have to provide the fields at all, that's a more complicated one...