Home » SQL & PL/SQL » SQL & PL/SQL » Possibility of Name (Oracle 11G)
Possibility of Name [message #670599] Mon, 16 July 2018 03:07 Go to next message
Genesys
Messages: 44
Registered: August 2010
Member
Hi,

have requirement to check the NAME in Block listed table(example base_table having world Block listed peoples name with alias , NAME and ALIAS are Columns).when new customer is going to create need to check the above table whether customer listed or not.

have written below PL/SQL Block to check the names.its working fine when NAME like 'AUGUSTO NAVARRO' or 'NAVARRO AUGUSTO' but I need suggesion if the the name having multiple string like 'AUGUSTO NAYA NAVARRO' as NAME so need the check possibility like 'AUGUSTO NAYA' or 'NAYA AUGUSTO' or 'AUGUSTO NAVARRO' or 'NAVARRO AUGUSTO' ...etc but i can extend the code to achieve this but performance wise its not good.can u please advise on this.

Note: base table having nearly 4 Millions of records.

   declare
     a varchar2(50) := 'AUGUSTO NAVARRO'; -- NAVARRO AUGUSTO
     c number := 0;
     d varchar2(50);
     e varchar2(50);
     p_name varchar2(50):='AUGUSTO NAVARRO';
     cursor cur is(
       SELECT 
        NAME
         FROM base_table
        WHERE UPPER(NAME) like UPPER(a)
          );
   begin
     a := '%' || replace(a, ' ', '%') || '%';
     dbms_output.put_line('name ' || a);
   
     SELECT count(*)
       into c
       FROM base_table
      WHERE UPPER(NAME) like UPPER(a)
        AND RECORD_STAT = 'O';
   
     if c = 0 then     
       d := substr(a, instr(p_name, ' ') + 1);
       e := substr(a, 1, instr(p_name, ' ')+1);
       dbms_output.put_line('first name ' || d);
       dbms_output.put_line('second name ' || e);
       a:=d  || e;
       --a := '%' || replace(a, ' ', '%') || '%';
       dbms_output.put_line('full name ' || a);
     end if;
   
     for rec in cur loop
     
       dbms_output.put_line(rec.name);
       c := c + 1;
       if c = 50 then
         exit;
       end if;
     end loop;
   end;

[mod-edit: code tags added by bb]

[Updated on: Fri, 27 July 2018 19:43] by Moderator

Report message to a moderator

Re: Possibility of Name [message #670601 is a reply to message #670599] Mon, 16 July 2018 06:50 Go to previous messageGo to next message
Bill B
Messages: 1910
Registered: December 2004
Senior Member
Are you looking for the following. If the name is 'AUGUSTO NAYA NAVARRO' , you want the first name set to AUGUSTO and the last name set to NAVARRO?
If so

lc_name := 'AUGUSTO NAYA NAVARRO' ;
lc_first_name := substr(lc_name,1,instr(lc_name,' ')-1);
lc_last_name := substr(lc_name,instr(lc_name,' ',-1)+1);
Re: Possibility of Name [message #670604 is a reply to message #670601] Mon, 16 July 2018 08:59 Go to previous messageGo to next message
leroyb_edi
Messages: 2
Registered: July 2018
Junior Member
If you are looking for more 'fuzzy' matches, you may want to check out either the SOUNDEX function, or the UTL_MATCH package - there is an excellent article in the Spring 2018 edition of Oracle scene and go to page 12. I hope this helps you out.

Bertrand
Re: Possibility of Name [message #670605 is a reply to message #670601] Mon, 16 July 2018 09:21 Go to previous messageGo to next message
EdStevens
Messages: 1142
Registered: September 2013
Senior Member
how do you handle if two different people happen to have the same name? I know for a fact that if I google my name I get a lot of hits on people who are not me. I am Ed Stevens, the DBA. I am not Ed Stevens, the First Baseman for the Dodgers then the Pirates during the 1940's. Nor am I Ed Stevens, the Preterist theologian. Nor am I Ed Stevens, Chairman and CEO of Stevens Strategic Communications. Nor am I Ed Stevens, character on a popular television show in the early 1980's.

Bottom line, one's name is not a unique identifier.
Re: Possibility of Name [message #670606 is a reply to message #670599] Mon, 16 July 2018 09:32 Go to previous messageGo to next message
Genesys
Messages: 44
Registered: August 2010
Member
Hi,

havn't got any reply but it shows replied 2 times
Re: Possibility of Name [message #670607 is a reply to message #670601] Mon, 16 July 2018 09:56 Go to previous messageGo to next message
Genesys
Messages: 44
Registered: August 2010
Member
hi,

lc_name := 'AUGUSTO NAYA NAVARRO' ;
lc_first_name := substr(lc_name,1,instr(lc_name,' ')-1);
lc_last_name := substr(lc_name,instr(lc_name,' ',-1)+1);


for this lc_name, i need to check the base table with below name (3*2 =6 TIMES).

AUGUSTO NAYA
AUGUSTO NAVARRO
NAYA NAVARRO
NAYA AUGUSTO
NAVARRO AUGUSTO
NAVARRO NAYA

it just a popup message to display all the possibility name on the screen.

Re: Possibility of Name [message #670630 is a reply to message #670604] Tue, 17 July 2018 03:18 Go to previous messageGo to next message
leroyb_edi
Messages: 2
Registered: July 2018
Junior Member
with link Oracle Scene issue 67 page 12
Re: Possibility of Name [message #670822 is a reply to message #670599] Fri, 27 July 2018 20:13 Go to previous message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
The following assumes that you want at least 2 of the names in a string that may contain 2 or 3 or more names to match the names in one value of the name column of the table, in any order.

-- test data:
SCOTT@orcl_12.1.0.2.0> select name from base_table
  2  /

NAME
--------------------
NAVARRO NAYA AUGUSTO
AUGUSTO NAYA
AUGUSTO NAVARRO
NAYA NAVARRO
NAYA AUGUSTO
NAVARRO AUGUSTO
NAVARRO NAYA
AUGUSTO GENESYS
GENESYS NAVARRO

9 rows selected.

-- variable and value:
SCOTT@orcl_12.1.0.2.0> variable p_name varchar2(50)
SCOTT@orcl_12.1.0.2.0> exec :p_name := 'AUGUSTO NAYA NAVARRO'

PL/SQL procedure successfully completed.

-- query:
SCOTT@orcl_12.1.0.2.0> select name
  2  from   base_table,
  3  	    (select regexp_substr (' ' || :p_name, '[^ ]+', 1, rownum) pname
  4  	     from   dual
  5  	     connect by level <= regexp_count (:p_name, ' ') + 1)
  6  where  instr (name, pname) > 0
  7  group  by name
  8  having count(*) >= 2
  9  /

NAME
--------------------
AUGUSTO NAVARRO
NAVARRO NAYA
NAYA AUGUSTO
AUGUSTO NAYA
NAVARRO NAYA AUGUSTO
NAVARRO AUGUSTO
NAYA NAVARRO

7 rows selected.
Previous Topic: How to ensure uniqueness on RAISE_APPLICATION_ERROR
Next Topic: return top 10 records based on dynamic count of prev records results
Goto Forum:
  


Current Time: Fri Nov 22 20:31:15 CST 2019