Re: Percent wildcard in dynamic statement doesnt work ??

A copy of this was sent to pepper_at_breedlove.ecn.purdue.edu (Pierre H Pero) (if that email address didn't require changing) On 12 Aug 1999 15:11:45 GMT, you wrote:

>I am new to oracle,sql,databases and am writing a C program to access a database.
>I can not get the % wildcard to work in a dynamic statement.
>
>The following is a statement I am trying to prepare and execute
>
>CREATE TABLE ECE_TEMP_1 AS SELECT * FROM ECE_INVENTORY WHERE UPPER( NAME ) LIKE 'L%TAL' ESCAPE '^'
>
>but it doesnt work.
>In the database there is a row with the name lytal.
>There is no other rows with a name at all similar to lytal.
>below are some variations of the like clause and if they work or not.
>
> WHERE UPPER( NAME ) LIKE 'LYTAL' works .
> WHERE UPPER( NAME ) LIKE 'L_TAL' works .
> WHERE UPPER( NAME ) LIKE 'L%%YTAL' works .
> WHERE UPPER( NAME ) LIKE 'LYTAL%%' works .
> WHERE UPPER( NAME ) LIKE 'L%YYTAL' works ! there is no lyytal
> WHERE UPPER( NAME ) LIKE 'L%_YTAL' works ! there is no l*ytal
> WHERE UPPER( NAME ) LIKE 'L%YTAL' doesnt work
> WHERE UPPER( NAME ) LIKE 'LYTAL%' doesnt work and error messages.
>

define "doesn't work"
share the error message with us.

show us the code you are using.

>The percent sign is not being used as a wildcard but
>I am left with the feeling that the % is at somepoint being used
>as an escape to ignore the following character.
>
>If I run sql directly the % wildcard works as I expect it should.
>
>Does anyone have some clues for the lost ??
>
>Pierre Pero
>pepper_at_ecn.purdue.edu

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Aug 12 1999 - 11:34:38 CDT