Wednesday, September 17, 2014

MSSQL - Reverse string searching in a table

Imagine this scenario.

I have a string, lets call it the Hash which is equal to "44F3D19B7176128263BDC"

Now, I have a separate table with a structure like this.
dbo.Ads

What I want to do is that find every record in this table which contains the "Hasher" value on our original string "44F3D19B7176128263BDC".

To do this, we can

  1. Loop through every record in the Ads table, 
  2. Get the Hasher value of that record, 
  3. Check whether this Hasher value contains in our original hash string. 

In SQL, we can do it very easy.

declare @hash varchar(20) = '44F3D19B7176128263BDC';

select * from [dbo].[Ads] 
where CHARINDEX([Hasher], @hash) > 0
order by DATALENGTH([Hasher]) desc