Perform Android Like Search in 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

Conclusion

So in this article, we learnt

1. Use of CROSS APPLY
2. How to use PATINDEX for searching

Hope this will be helpful. Thanks for reading. Zipped file attached.