Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: a substring question
On Mon, 26 May 1997 14:26:11 -0500, sanjay rallapally <c696346_at_showme.missouri.edu> wrote:
>hello again,
>
>how do i search for a string in a larger string ?
>please don't tell me to use substr 'cuz i tried that
>eg:
> 224, Lewis Hall
>
>User input:
> lewis
>
>SQL statement:
> select emp_name from employees
> where substr( upper(emp_name),1) LIKE upper(v_address);
>
>where v_address contains lewis
>Can anyone point out my mistake and/or a better way to search for a
>string( in this case 'lewis') in a larger string( 224, Lewis Hall) ?
In this particular instance, I suspect the problem was the use of LIKE
against a non-wildcard string (i.e., 'lewis' instead of '%lewis%').
Thus, the emp_name field would have to be 'LEWIS' for like to match
against 'LEWIS'. 'ANTHONY LEWIS', 'A LEWIS', and other variations
would *not* result in a match because you did not give like a wildcard
to use.
The other responses are also correct. You can use instr() instead, which theoretically should perform better than using LIKE against a variable string.
Jonathan Ingram Received on Tue May 27 1997 - 00:00:00 CDT