Problem Statement:
Today, we are going to see a method to find the number of columns with exact match of a search word in a table.

This can be achieved in a very simple way using XQUERY. I just would like to share the code sample that I tried to get the information.

DDL and Sample Data:

create table test_ExactMatchsearch
(
id int identity(1,1),val1 varchar(200), Val2 varchar(200)
)

insert into test_ExactMatchsearch values('SQL','Server')
insert into test_ExactMatchsearch values('BizTalk','Server')
insert into test_ExactMatchsearch values('PLSQL','Oracle')
insert into test_ExactMatchsearch values('PL','SQL')
insert into test_ExactMatchsearch values('SQLServer','SQL')
insert into test_ExactMatchsearch values('SQL','SQL')

In the above table I would like to search “SQL” and find the presence of the exact search word.

Actual Code:

declare @SearchWord varchar(10)
set @SearchWord = 'SQL'

--How to find the number of columns available the exact word.
;with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
select *,
       (
          select *
 from test_ExactMatchsearch as T2
 where T1.id = T2.id
          for xml path('row'), elements xsinil, type 
       ).value('count(//*[text()=sql:variable("@SearchWord")])','varchar') as ExactMatchColumnCount
from test_ExactMatchsearch as T1

Result:

Clean up the Table object:

Drop table test_ExactMatchsearch