Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!news.he.net!chekhov.conxion.net!news.oracle.com!not-for-mail From: Martin Doherty Organization: Oracle Corporation User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.1) Gecko/20020826 X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.databases.oracle.misc Subject: Re: Querying for Strings with containing Wildcards References: <3DD019EB.56E595F4@exesolutions.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Lines: 95 Message-ID: Date: Mon, 11 Nov 2002 14:04:28 -0800 NNTP-Posting-Host: 130.35.56.38 X-Trace: news.oracle.com 1037052505 130.35.56.38 (Mon, 11 Nov 2002 14:08:25 PST) NNTP-Posting-Date: Mon, 11 Nov 2002 14:08:25 PST Xref: newsfeed1.easynews.com comp.databases.oracle.misc:89229 X-Received-Date: Mon, 11 Nov 2002 15:04:27 MST (news.easynews.com) Doesn't instr return 0 if the search argument is not found? Also '%' only has significance when used with the LIKE operator, for all other functions and operators it is simply another characters. So, where instr (column_name, '%') > 0 should show all rows that have a '%' somewhere in that column. If you need to search many columns in each row then you will have to apply the instr function to each possible column. where instr (column1, '%') > 0 or instr (column, '%') > 0 or instr (column, '%') > 0 or instr (column, '%') > 0 etc. If you have a lot of columns in a lot of tables, you might want to invest the time to develop some code that will generate a script of update statements, applying the TRANSLATE or REPLACE functions to any character-type column. Then test, test, test .... Martin Hulk wrote: >where instr(field,chr(37)) > 1; > >;-D Works at any rate .... thanks much > > >"Daniel Morgan" wrote in message >news:3DD019EB.56E595F4@exesolutions.com... > > >>Hulk wrote: >> >> >> >>>Hey guys, >>> >>>I was wondering if I could get a hint on querying for strings containing >>> >>> >a > > >>>wildcard, notable the % sign. Against our advice one our clients has >>>created many values in their database containing percent signs and would >>> >>> >now > > >>>like a list of the appropriate records so they can delete them. >>> >>>Obviously the following does not work, >>> >>>select field from table where field = 'xx%' >>> >>>I tried the following >>> >>>set escape \ >>> >>>select field from table >>> >>>where field = 'xx\%'; >>> >>>This kinda worked excpet it does not seem to allow us to search for any >>> >>> >and > > >>>all records containing a percent sign. >>> >>>Any tips of advice would be appreciated. >>> >>> >>Try the following SQL >> >>select ascii('%') from dual; >> >>I think it will lead you in a direction that will work. >> >>Daniel Morgan >> >> >> > > > >