How add more rows when find string in column Oracle

Would it be possible to add more rows base on Keyword string in SQL ?

table A

PID    PromotionName                                
 1      OUT_EC_D10_V500K_FamilyCare_PROCO
 2      OUT_EC_D5_V50K_Lunchbox_PROCO
 3      OUT_EC_D5_V50K_PROCO

table B

promotion_code     itm_name         quantity

Lunchbox           Item name 1         1
FamilyCare         Item name 2         1
FamilyCare         Item name 3         1
BUY1FREE6          Item name 4         1
HiSummer           Item name 5         1
FamilyCare         Item name 6         1 

Example:

SELECT * FROM A where pid = '1';

Output of the SQL should be –

PID   PromotionName                          Itm_name          quantity
 1     OUT_EC_D10_V500K_FamilyCare_PROCO
 2     FamilyCare                           Item name 2         1
 3     FamilyCare                           Item name 3         1
 4     FamilyCare                           Item name 6         1 

How to find string with keyword ‘FamilyCare’ in PromotionName of table A base on promotion_code of table B? If it exist it will add more rows in output

Any help with the SQL?

Answer

Here is how you can achieve this:

SELECT PID,PromotionName, '' as Itm_name, NULL as quantity 
FROM A 
WHERE pid = '1'

UNION

SELECT PID, PROMOTION_NAME, Itm_name, quantity 
FROM 
(SELECT * FROM A inner join B on a.promotionName LIKE '%'||b.promotion_name||'%')
WHERE pid='1'

You have to update your pid in both the places (before and after UNION).

Notice that tables were joined using LIKE operator with % before and after the word. Hence this joins if a part of a string is present in another column.

db<>fiddle link here