Re: Case insensitive comparisons

From: Neil Emery <neil.emery_at_virgin.net>
Date: 2000/05/23
Message-ID: <8ge04k$dqh$1@nnrp1.deja.com>#1/1

Alexander,

I am assuming this is for pl/sql rather than forms (in which case just set the case insensitive query property)

This method uses the principles of the case insensitive query from a Developer 2000 form

select * from part
where upper(part_no) = 'FRED'
and (part_no like 'Fr%'

      or part_no like 'FR%'
      or part_no like 'fr%'
      or  part_no like 'fR%')

SELECT STATEMENT Optimizer=RULE
  CONCATENATION
    TABLE ACCESS (BY ROWID) OF PART
      INDEX (RANGE SCAN) OF PK_PART (UNIQUE)     TABLE ACCESS (BY ROWID) OF PART
      INDEX (RANGE SCAN) OF PK_PART (UNIQUE)     TABLE ACCESS (BY ROWID) OF PART
      INDEX (RANGE SCAN) OF PK_PART (UNIQUE)     TABLE ACCESS (BY ROWID) OF PART
      INDEX (RANGE SCAN) OF PK_PART (UNIQUE) For a universal solution that can be applied to any column in the database, define the following functions :

function up_low(p_string in varchar2) return varchar2 is   v_return varchar2(10);
begin
  v_return := upper(substr(p_string,1,1))||lower(substr(p_string,2,1))
||'%';

  return v_return;
end;

function low_low(p_string in varchar2) return varchar2 is   v_return varchar2(10);
begin
  v_return := lower(substr(p_string,1,1))||lower(substr(p_string,2,1))
||'%';

  return v_return;
end;

function up_up(p_string in varchar2) return varchar2 is   v_return varchar2(10);
begin
  v_return := upper(substr(p_string,1,1))||upper(substr(p_string,2,1))
||'%';

  return v_return;
end;

function low_up(p_string in varchar2) return varchar2 is   v_return varchar2(10);
begin
  v_return := lower(substr(p_string,1,1))||upper(substr(p_string,2,1))
||'%';

  return v_return;
end;

Also create a function which returns the upper string and checks for the presence of a wild card at the end of the string and appends this to the uppercased string

function up_wild(p_string in varchar2) return varchar2 is

  v_return             varchar2(2000);
  v_wild                 varchar2(1);

begin
  v_return := upper(p_string);
  v_wild := substr(p_string,length(p_string),1)) ;   if v_wild in ('%', '_'); then
    v_return := v_return || v_wild;
  end if;
  return v_return;
end;

so the sql becomes:

select * from part
where upper(part_no) like up_wild(:name) and (part_no like up_low(:name)

 or part_no like low_up(:name)
 or part_no like low_low(:name)
 or  part_no like up_up(:name)

Hope This Helps

Neil

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue May 23 2000 - 00:00:00 CDT

Re: Case insensitive comparisons - no way?