Prev: Output padded result
Next: Simple query question
From: Learner on 5 Aug 2010 12:23
Hi,

I have around 13 columns in a table and building a front end where
user enters a value in a text box and hit submit and the SQL query
should return all the rows that matched columns with the value entered
by the user.

Can some one please help me with how to come up with a sql please.

Actullay I need to write this in Oracle 10g but I am trying it to work
in SQL server and hopefully it works the same in Oracle too. I do not
have the much data in Oracle on the test box so trying to get it
working in on my sql box. The below simple sql seems working OK but
want to know if it could be any better and work in all cases

DECLARE @STRING VARCHAR(30)
SET @STRING = '%Dell%' (percent symbols before and after picks up if
the text has any spaces)
select * from <TableName>
where col1 like @STRING
or col1 like @STRING
or col2 like @STRING
OR col3 like @STRING
OR col4 LIKE @STRING
OR col5 LIKE @STRING
OR col6 LIKE @STRING
OR col7 LIKE @STRING
OR col8 LIKE @STRING
OR col9 LIKE @STRING
OR col1 LIKE @STRING
etc... same as the remaining columns.

Thanks in advance.

L
From: Gert-Jan Strik on 5 Aug 2010 14:21
Your query is correct. Don't expect great performance, because this
query requires a table scan.

In general, it doesn't make much sense to search data in multiple
columns. In a properly normalized table, each column would have a
different meaning, a different purpose. A blanket search seems odd.
Also, it requires the user to investigate all 13 columns of the
resultset to check which column actually matched.

--
Gert-Jan


Learner wrote:
>
> Hi,
>
> I have around 13 columns in a table and building a front end where
> user enters a value in a text box and hit submit and the SQL query
> should return all the rows that matched columns with the value entered
> by the user.
>
> Can some one please help me with how to come up with a sql please.
>
> Actullay I need to write this in Oracle 10g but I am trying it to work
> in SQL server and hopefully it works the same in Oracle too. I do not
> have the much data in Oracle on the test box so trying to get it
> working in on my sql box. The below simple sql seems working OK but
> want to know if it could be any better and work in all cases
>
> DECLARE @STRING VARCHAR(30)
> SET @STRING = '%Dell%' (percent symbols before and after picks up if
> the text has any spaces)
> select * from <TableName>
> where col1 like @STRING
> or col1 like @STRING
> or col2 like @STRING
> OR col3 like @STRING
> OR col4 LIKE @STRING
> OR col5 LIKE @STRING
> OR col6 LIKE @STRING
> OR col7 LIKE @STRING
> OR col8 LIKE @STRING
> OR col9 LIKE @STRING
> OR col1 LIKE @STRING
> etc... same as the remaining columns.
>
> Thanks in advance.
>
> L
From: Learner on 5 Aug 2010 14:24
On Aug 5, 2:21 pm, Gert-Jan Strik <sorrytoomuchspamalre...(a)xs4all.nl>
wrote:
> Your query is correct. Don't expect great performance, because this
> query requires a table scan.
>
> In general, it doesn't make much sense to search data in multiple
> columns. In a properly normalized table, each column would have a
> different meaning, a different purpose. A blanket search seems odd.
> Also, it requires the user to investigate all 13 columns of the
> resultset to check which column actually matched.
>
> --
> Gert-Jan
>
>
>
> Learner wrote:
>
> > Hi,
>
> >   I have around 13 columns in a table and building a front end where
> > user enters a value in a text box and hit submit and the SQL query
> > should return all the rows that matched columns with the value entered
> > by the user.
>
> > Can some one please help me with how to come up with a sql please.
>
> > Actullay I need to write this in Oracle 10g but I am trying it to work
> > in SQL server and hopefully it works the same in Oracle too. I do not
> > have the much data in Oracle on the test box so trying to get it
> > working in on my sql box. The below simple sql seems working OK but
> > want to know if it could be any better and work in all cases
>
> > DECLARE @STRING VARCHAR(30)
> > SET @STRING = '%Dell%' (percent symbols before and after picks up if
> > the text has any spaces)
> > select * from <TableName>
> > where col1 like @STRING
> > or col1 like @STRING
> > or col2 like @STRING
> > OR col3 like @STRING
> > OR col4 LIKE @STRING
> > OR col5 LIKE @STRING
> > OR col6 LIKE @STRING
> > OR col7 LIKE @STRING
> > OR col8 LIKE @STRING
> > OR col9 LIKE @STRING
> > OR col1 LIKE @STRING
> > etc... same as the remaining columns.
>
> > Thanks in advance.
>
> > L- Hide quoted text -
>
> - Show quoted text -

Thanks for the note. Yes we do have such things here and there in few
applications. So just wanted to do it right. So I will go ahead and
use the same query to get the results.

Thanks again.
 | 
Pages: 1
Prev: Output padded result
Next: Simple query question