How to use like operators when input name is “Obe ” Wanna match both column for the input name

SQL Select * from name where inputname like (‘%’ || (trim(firstname) || ‘%’));

CodePudding user response：

If I understood you correctly, that would be

SQL> with test (lastname, firstname) as
  2    (select 'Mr.', 'Bean' from dual union all
  3     select 'Mr.', 'Jobe' from dual
  4    )
  5  select *
  6  from test
  7  where lower(firstname) like '%' || '&par_input_name' || '%';
Enter value for par_input_name: obe

LAS FIRS
--- ----
Mr. Jobe

Or, maybe with the instr function?

<snip>
  5  select *
  6  from test
  7  where instr(lower(firstname), '&par_input_name') > 0;
Enter value for par_input_name: obe

LAS FIRS
--- ----
Mr. Jobe

SQL>

If you want to check both columns, modify where clause, e.g.

where instr(lower(firstname), '&par_input_name') > 0
   or instr(lower(lastname) , '&par_input_name') > 0;

As I used SQL*Plus for demonstration (and substitution variable), you might need to switch to a bind variable:

... where instr(lower(firstname), :par_input_name) > 0 ...

or use procedure's (function's parameter) or whatever you have to pass that value.

CodePudding user response：

Use OR to compare one column or the other column and convert the input and the column values to the same case:

SELECT *
FROM   name
WHERE  UPPER(firstname) LIKE '%' || UPPER(:inputname) || '%'
OR     UPPER(lastname) LIKE '%' || UPPER(:inputname) || '%'