Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: a substring question

Re: a substring question

From: Jonathan Ingram <jingram_at_teleport.com>
Date: 1997/05/27
Message-ID: <338b5323.111972337@news.teleport.com>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US