Possibility of Name [message #670599] |
Mon, 16 July 2018 03:07 |
Genesys
Messages: 45 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 |
Bill B
Messages: 1971 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 #670607 is a reply to message #670601] |
Mon, 16 July 2018 09:56 |
Genesys
Messages: 45 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 #670822 is a reply to message #670599] |
Fri, 27 July 2018 20:13 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|