Find the number of columns with exact match of a search word using XQUERY in SQL Server

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:
ExactMatchScrrenshot
Clean up the Table object:


Drop table test_ExactMatchsearch
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s