how to ignore the data based on specific keywords?-CodePudding

Want to ignore the row no 2 and 3 which is not have any space based on 2nd table data . Table 1 like below

  No    Name 
    1   kumar unknow raja
    2   kumarunknowraja sameple
    3   kumartradraja testing
    4   kumar trad raja
    5   trad
    6   unknow

Table 2

Name
unknow
trad

Using this query but l am getting all the records select * from base_tbl where exists (select name from exc_tbl);

CodePudding user response：

Here is an ms sql solution. It is an ugly piece of sql though....

with data(No,Name ) as(
select 1,   'kumar unknow raja' union all
select 2,   'kumarunknowraja sameple' union all
select 3,   'kumartradraja testing' union all
select 4,   'kumar trad raja' union all
select 5,   'trad' union all
select 6,   'unknow' 
),
param (name) as (
select 'unknow' union all
select 'trad'
),
data2 (No,Name ) as(
select No,data.Name from data
join param on data.name = param.name 
union all
select No,data.Name from data
join param on data.name like ('% '   param.name   '%')
union all
select No,data.Name from data
join param on data.name like ('%'   param.name   ' %')
)
select distinct * from data2
order by No

But it gives you what you want: