Sometime back, in the DNF forum a question has been asked about implementing an search like Android one. This post is an attempt to provide an answer for the same using TSQL.
Introduction
Sometimes back while answering a DNF forum question , we came across an interesting situation where the person asked about implementing an search like Android one. We have presented a solution for that using C#. In this article we will address the same problem and present a solution for it but using TSQL.
Using the code
Let us first create the table @tblPerson and populate some data to it.
--@tblPerson declaration
DECLARE @tblPerson TABLE (PersonName VARCHAR(100))
-- Insert some records to the @tblPerson
INSERT INTO @tblPerson
VALUES
('Rajlakshmi Biswas'),('Niladri Biswas'),('Arina Biswas'),('RNA Team'),
('Mahesh Nagar'),('Manish Nagar'),('Shan mani'),('Ghanshaym rama')
--Project the records from @tblPerson
SELECT *
FROM @tblPerson
The result is

Now we can write the below lines of code
--set the search term
DECLARE @toSearch VARCHAR(20) = 'MA'
-- set the delimeter
DECLARE @delimeter VARCHAR(10) = ' '
The next objective is to visit each row of the table @tblPerson and split that value based on the @delimeter provided.
--split records based on the delimeter
SELECT
X.PersonName,
Y.SplitNames
FROM
(
SELECT
PersonName,
ReplaceValue = CAST('<X>'+REPLACE(p.PersonName,@delimeter,'</X><X>')+'</X>' AS XML)
FROM @tblPerson p
)X
CROSS APPLY
(
SELECT
SplitNames = Data.d.value('.','varchar(50)')
FROM X.ReplaceValue.nodes('X') AS Data(d)
)Y
When we run the above query we get the below output

First of all we cast the PersonName column of table @tblPerson into an XML data type by replacing the delimiter with starting and ending
tags <X></X>. The CROSS APPLY clause allows to join a table to a table-valued-function. It only returns rows from the left side (@tblPerson) if the table-valued-function returns rows.
The next step will be to check the presence of search term (@toSearch) in the splitted values. For this to perform, we will employ PATINDEX() as under
SELECT
X.PersonName,
Y.SplitNames,
--Search for the matched pattern
PATINDEX ( '%'+ @toSearch +'%' , LEFT(Y.SplitNames,LEN(@toSearch)))
FROM .....
The result

So we achieved our main objective. The next is the to display the output as asked in the question. So, let us modify our query a little bit
SELECT
X.PersonName
,Y.SplitNames
, CASE
WHEN
--Search for the matched pattern
PATINDEX ( '%'+ @toSearch +'%' , LEFT(Y.SplitNames,LEN(@toSearch))) > 0
THEN
--perform the display/action
REPLACE(X.PersonName,@toSearch,'<b>' + @toSearch + '</b>')
ELSE Y.SplitNames
END AS ReplacedValues
FROM .....
The result

We can figure out that, not only the valid records are present but also the unwanted records are there which needs to be remove. This can be achieve by applying the condition in the where clause
WHERE PATINDEX ( '%'+ @toSearch +'%' , LEFT(Y.SplitNames,LEN(@toSearch))) = 1
The complete query is presented below
--@tblPerson declaration
DECLARE @tblPerson TABLE (PersonName VARCHAR(100))
-- Insert some records to the @tblPerson
INSERT INTO @tblPerson
VALUES
('Rajlakshmi Biswas'),('Niladri Biswas'),('Arina Biswas'),('RNA Team'),
('Mahesh Nagar'),('Manish Nagar'),('Shan mani'),('Ghanshaym rama')
--Project the records from @tblPerson
--SELECT *
--FROM @tblPerson
--set the search term
DECLARE @toSearch VARCHAR(20) = 'MA'
-- set the delimeter
DECLARE @delimeter VARCHAR(10) = ' '
SELECT
X.PersonName
, CASE
WHEN
--Search for the matched pattern
PATINDEX ( '%'+ @toSearch +'%' , LEFT(Y.SplitNames,LEN(@toSearch))) > 0
THEN
--perform the display/action
REPLACE(X.PersonName,@toSearch,'<b>' + @toSearch + '</b>')
ELSE Y.SplitNames
END AS ReplacedValues
FROM
(
SELECT
PersonName,
ReplaceValue = CAST('<X>'+REPLACE(p.PersonName,@delimeter,'</X><X>')+'</X>' AS XML)
FROM @tblPerson p
)X
CROSS APPLY --split records based on the delimeter
(
SELECT
SplitNames = Data.d.value('.','varchar(50)')
FROM X.ReplaceValue.nodes('X') AS Data(d)
)Y WHERE PATINDEX ( '%'+ @toSearch +'%' , LEFT(Y.SplitNames,LEN(@toSearch))) = 1
The result is

Change: @toSearch VARCHAR(20) = 'SH'; and the result

References
a)
CROSS APPLY
b)
PATINDEX
Conclusion
So in this article, we learnt
- Use of CROSS APPLY
- How to use PATINDEX for searching
Hope this will be helpful. Thanks for reading. Zipped file attached.