Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

LIKE operator- change symbol for [all characters]

WestDraytonFeb 17 2010 — edited Feb 19 2010
I have an input parameter which contains symbols "*" (star symbol), example:

i_search_par := 'country * street';

I gonna do query:

select * from SearchTable
where col1 like i_search_par;

I want that the "*" would act the same way as "%"-symbol in LIKE operator.

So i want that query would return such results:

'country 1 street'
'country 200 street'

and so on.

What is the better way to achieve this, that "*" is going to be interpreted as a "any number of any symbols"?

Maybe i can say something like:

SET SESSION ANYCHAR SYMBOL = "*".
And after doing that Oracle understands in LIKE operators that star-symbol is like i want?

Or should i really do string replacement by replacing ""%" to "*" in "i_search_par" and in other 30 such varaibles?
This post has been answered by MichaelS on Feb 19 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 19 2010
Added on Feb 17 2010
10 comments
1,110 views