AppletTalk.com Java discussions newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Simulating wildcard for Numeric database types          AppletTalk.com Forum Index -> Java and Databases View previous topic :: View next topic   Author Message goykhmanster Guest Posted: Fri Apr 27, 2007 7:46 pm    Post subject: Simulating wildcard for Numeric database types 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. Back to top Robert Klemme Guest Posted: Fri Apr 27, 2007 8:17 pm    Post subject: Re: Simulating wildcard for Numeric database types On 27.04.2007 16:46, goykhmanster wrote: Quote: 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. 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 Back to top goykhmanster Guest Posted: Fri Apr 27, 2007 8:24 pm    Post subject: Re: Simulating wildcard for Numeric database types Nevermind, i figured it out.. TO_CHAR(COLNAME) LIKE '%' does the trick. Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         AppletTalk.com Forum Index -> Java and Databases All times are GMT Page 1 of 1   Jump to: Select a forum Java Newsgroups----------------Java DiscussionsWeb-based java games comp.lang.java 3D Graphics API's for Java Java: Support and criticism Java System Announcements comp.lang.java.api JavaBeans Java and CORBA Java and Databases comp.lang.java.developer Java GUI Toolkits Java Help comp.lang.java.javascript JVM, native methods and hardware comp.lang.java.misc Java Language Programming Security and Java comp.lang.java.setup Java Software Tools comp.lang.java.tech ibm.software.java.linux  Non-English Java Newsgroups----------------Java Language (German)Language Java (Français)Java (Italian)  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.