Simulating wildcard for Numeric database types

Thread view:

goykhmanster - 27 Apr 2007 15:46 GMT

Hi

I have a query that is expecting multiple numeric parameters, and I am
trying to make it generic enough so that if any of these numeric
parameters is not provided, i can still execute the query and get all
matches as if i'm using '%' for a Varchar parameter. The reason for
this is that I'm trying to implement a search algorithm and expect
users to select only a few out of possible search filters, but each
one of them submits a numeric ID of a database record. I know
wildcards (at least in Oracle and MySQL, can't vouch for other DBs)
are allowed only for alphabetic types, this is why so far the only way
to do this is to change all numeric IDs from type INTEGER to VARCHAR2
in the database, but somehow it doesn't feel as the right way of doing
this. Writing if/else logic and having multiple query objects doesn't
seem as the right way either. If anyone has any thoughts, i'd
appreciate it. Thanks.

Reply to this Message

Robert Klemme - 27 Apr 2007 16:17 GMT

> I have a query that is expecting multiple numeric parameters, and I am
> trying to make it generic enough so that if any of these numeric
[quoted text clipped - 10 lines]
> seem as the right way either. If anyone has any thoughts, i'd
> appreciate it. Thanks.

Can't you just generate the SQL based on the parameters present? Note
also that this might prove to be difficult to make efficient, i.e. to
get the indexing right. If you provide DDL and queries you might get
more specific hints.

Kind regards

robert

Reply to this Message

goykhmanster - 27 Apr 2007 16:24 GMT

Nevermind, i figured it out.. TO_CHAR(COLNAME) LIKE '%' does the
trick.

Reply to this Message

Adam Maass - 30 May 2007 05:47 GMT

> Nevermind, i figured it out.. TO_CHAR(COLNAME) LIKE '%' does the
> trick.

But that makes me shudder. Unless the DB's query optimizer is very smart,
this will result in a lot of extra work. Much better to dynamically generate
the SQL.

Reply to this Message

Robert Klemme - 30 May 2007 10:35 GMT

>> Nevermind, i figured it out.. TO_CHAR(COLNAME) LIKE '%' does the
>> trick.
>
> But that makes me shudder. Unless the DB's query optimizer is very
> smart, this will result in a lot of extra work. Much better to
> dynamically generate the SQL.

Absolutely! Then the only problem left is to get the indexing right. :-)

robert

Reply to this Message

Lulu58e2 - 30 May 2007 19:52 GMT

> Hi
>
> I have a query that is expecting multiple numeric parameters, and I am
> trying to make it generic enough so that if any of these numeric
> parameters is not provided, i can still execute the query and get all
> matches as if i'm using '%' for a Varchar parameter.

I'm not saying this is right, optimal or good, but I use:

where mySearchValue = -1 or my_column = mySearchValue

If the search value is -1 (or whatever default you choose) then the
records are not filtered. If the search value is not the default, then
the records are filtered.

Granted, I haven't used it with massive queries but it seem to work
alright.

C>

Reply to this Message

Simulating wildcard for Numeric database types

Free Magazines