Using the LIKE command in an Oracle function

[codehead]

Does anyone know what the Oracle function syntax would be for the following pseudo code:

LIKE %(UPPER(VariableName))%

where VariableName is an input variable? I am trying to do both an UPPER and a LIKE command at once. I think it should look something like this:

LIKE ''% ' || UPPER('''||ElementName||''') || ' %''

but I just can't seem to get the quotes right in the function (it works as a straight SQL query, but not as a function).

Or, if anyone knows where the rules for quotes in Oracle functions are written, that would help too.

Thanks!

Codehead

 

[Khayyam]

like upper(variable||%)

 

[jee]

like '%' || upper (variable) || '%'

or

like upper ('%' || variable || '%')


when you say you are using it within a function, I assume you are trying to do something like this??

If (x like upper ('%' || variable || '%') then
something
else
something else
end if;

 

[codehead]

Hello All:

I should have stated that this is being used in an Oracle PL/SQL "function" (not a regular built-in function).

It works as the following straight SQL query (but not as a PL/SQL function):

LIKE '%' || UPPER('Primary User') || '%'

I should have stated that this is being used in an Oracle PL/SQL function (not a regular built-in function).


Or, if anyone knows where the rules for quotes in Oracle functions are written, that would help too.

Thanks!

 

[sem]

The rules for using quotes are quite simple:
Only single quote has special meaning. String literals are quoted. Single quotes within string literal are doubled. The code provided by Jee will work both in function and in sql. I'm not sure I understand how do you use it. If you need some case insensitive instr() function (var1 like '%'||var2||'%' is equivalent to instr(var1,var2)<>0), the code may be

create function instr_ci(pstr in varchar2, ptest_str in varchar2)
return boolean
as
begin
if instr(upper(pstr),upper(ptest_str))<>0 then
return true;
else
return false;
end if;
end;

 

[codehead]

Hello All:

I finally got it:

LIKE UPPER(''%'||VariableName||'%'')

Note that it is *white space sensitive*.

Thanks for everyone's help!