Query multiple fields at once??

I have a table with 10 fields. I want to select any row that contains
the word "bob" in any one of the 10 fields. I've been writing this
query by checking each field individually connected with an OR
statement. Such as...
Select * from tablename where (column1 like '%bob%') or (column2 like
'%bob%') or ... and so on.
Is there an easier way to search all fields/columns in a more simple
SQL query?
The concept of ... Select * from tablename where (* like '%bob%')
?
Thanks,
- SteveNope. Your method of using the [OR] is the only way.
That you have to search multiple columns hints of denormalized data. Perhaps
this type of operation would be simpler if the table schema was
reconsidered.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegroups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>|||Hi Stephen
This sounds like your design may want further normalisation?
I guess you could do something like
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
'|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
like '%bob%'
so long as you are not looking for something containing the delimiter! I am
not sure how that would perform. Other possible options might be:
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column1 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column2 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column3 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column4 like '%bob%'
etc ...
If you know that only one column contained the search string then you could
use UNION ALL in the above method.
or
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
FROM ( Select column1 AS Searchcolumn, column1, column2, column3, column4,
column5, column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column2 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column3 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column4 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column5 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename ) A
where Searchcolumn like '%bob%'
HTH
John
"kennedystephen" wrote:
> I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>|||And if it were equality instead of like, you could reverse the clause, e.g.
WHERE 'bob' IN (column1, column2, column3, ...)
But I agree that the design does not seem fundamentally sound to me.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0256D861-C01E-4766-BE20-09ED5790337B@.microsoft.com...
> Hi Stephen
> This sounds like your design may want further normalisation?
> I guess you could do something like
> Select column1, column2, column3, column4, column5, column6, column7,
> column8, column9, column10
> from tablename
> where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
> ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
> '|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
> ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
> like '%bob%'
> so long as you are not looking for something containing the delimiter! I
> am
> not sure how that would perform.|||stephen,
If this is a common requirement for your applications, then you might want
to consider full text indexing and searches. BOL can get you started.
-- Bill
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegroups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>