Re: Querying for Strings with containing Wildcards

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

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" <dmorgan_at_exesolutions.com> wrote in message
>news:3DD019EB.56E595F4_at_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
>>
>>
>>
>
>
>
>
Received on Mon Nov 11 2002 - 16:04:28 CST