Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL logic - Partial macthing between two columns

S567Sep 30 2021

I have a requirement where i have two tables A and B
Table Source (text,category)- text feild can contains full names,firstnames,last names and other values too which are not relate to names, cateogory is NULL
Table Names -(person)- values in person feild can be first name, last name, full name any
My requirement is I need to update category field as People_name for which ever names that matches with Source.text=Names.person
Here tricky thing is we should not do straight forward match something we should use like regular expression or other techinque to see if any partial matches for firstname,lastname or fullnames matches in table Names then assign category to People_name.
image.png
See above category is updated as People_Name for text Joseph Tornor as there are partial names matched (firstname,lastname,fullnamesmatches ) in Table Names

Below is source data...
with source as
(select 'Joseph Torner' text , 'category' from dual union all
select 'Bill' text , 'category' from dual union all
select 'Maria Clogan' text , 'category' from dual union all
select 'Sesha Siri' text , 'category' from dual union all
select 'Msoffice' text , 'category' from dual union all
select 'Powerpoint' text , 'category' from dual)
with Names as
(select 'Tornor' text from dual union all
select 'Joseph' text from dual union all
select 'Joseph Torner Lary' text from dual )

Pls do let me know if any questions..

Comments
Post Details
Added on Sep 30 2021
27 comments
2,548 views