Here is a way of getting the results you require.
Using
like
to find the corresponding names in summary.
Then getting the
charindex
of the name and the star * position after the name.
Using a bit of
lead over
to figure out if a star exists after the name but not before the next name in the summary.
Here is a cte query of how it can be done:
with Summary as(
select 1 id,'asdffgggggg Anand * edkkofffmfmmfmfm Bala sdkdodkekeke Chandra dkkdkd "vinoth"*' summary
union all select 2, 'asdffgggggg Dinesh * edkkofffmfmmfmfm Frankin sdkdodkekeke Elisia dkkdkd Ganesh.'
union all select 3, 'asdffgggggg Hansika edkkofffmfmmfmfm [A.Ishwariya]* sdkdodkekeke Jack dkkdkd "Lalitha"'
), Name as (
select 1 id,'A.Ishwariya' name
union all select 2 id,'Anand'
union all select 3 id,'Bala'
union all select 4 id,'Chandra'
union all select 5 id,'Dinesh'
union all select 6 id,'Elisia'
union all select 7 id,'Frankin'
union all select 8 id,'Ganesh'
union all select 9 id,'Hansika'
union all select 10 id,'Jack'
union all select 11 id,'Lalitha'
union all select 12 id,'Vinoth'
), SummaryName as (
select
s.id SummaryId,
s.summary,
n.id NameId,
n.name,
lead(s.id) over(order by s.id) NextSummaryId,
len(s.summary) LenSummary
from Summary s
inner join Name n
on s.summary like '%'+n.name+'%'
), SummaryNameStarPos as (
select
top(1000)
*,
charindex(sn.name, sn.summary) NmPos,
charindex('*', sn.summary, charindex(sn.name, sn.summary)) NextStarPos,
isnull(lead(charindex(sn.name, sn.summary)) over(order by SummaryId, charindex(sn.name, sn.summary)), 0) NextNmPos
from SummaryName sn
)
select
*
from SummaryNameStarPos
where NextStarPos > NmPos
and (NextStarPos < NextNmPos or (NextSummaryId <> SummaryId and NextStarPos <= LenSummary))
where not (NextStarPos > NmPos
and (NextStarPos < NextNmPos or (NextSummaryId <> SummaryId and NextStarPos <= LenSummary))
)
It works with the sample data you have supplied.
You may need to modify the where clauses a little with the original data, but you should be able to see what is being compared to get the final results.
Ensure the records are ordered correctly, where they should be ordered by summary and name position, otherwise the
lead over
will not work as expected.
Probably not an efficient way of doing it.
Also take into consideration of advise from solution 1 of splitting out the summary data.
Hope that helps out.