How to shuffle search data in sql server

Question

I have table with column firstname. I have input for example ABC, the problem here Im not only want to search firstname = ABC, but also CAB or CBA or BAC.

Any idea how to do this.

Thanks

Answer 1

First create function which gets a string (e.g.'ABC') and returns a table with all permutations of given string (e.g. 'ABC','ACB','BAC','BCA','CAB','CBA')

I have created such a function based on java implementation from here :

CREATE FUNCTION [dbo].[permuteString] (@beginStr varchar(10),@endStr varchar(10))
RETURNS @result table (result varchar(10)) 
AS
BEGIN
     declare @i int
     declare @curStr varchar(10)
     if LEN(@endStr) <= 1
           insert into @result select @beginStr+@endStr
     else
           begin
                set @i = 1
                while(@i <= LEN(@endStr))
                begin
                  set @curStr = case when @i > 1 then substring(@endStr,1, @i-1) else '' end
                                + substring(@endStr, @i + 1, LEN(@endStr)-@i)        
                  insert into @result 
                  select * from dbo.permuteString(@beginStr + substring(@endStr,@i,1), @curStr)
                  set @i = @i+1
                end
           end  
    return 
END

Now when you have this function, use it along with in statement in your query :

select columnName
from tableName
where columnName in (select * from dbo.permuteString('',@inputString))

You can see more implementations of permutations function here : The most elegant way to generate permutations in SQL server

Answer 2

Partial Answer

Seems like a fun problem to solve. The easy part is splitting the 'ABC' into individual characters and then creating a Cartesian product to combine them into their unique combinations. The hard part is creating the dynamic SQL to handle the Cartesian product.

Using abcd to better illustrate the Cartesian product portion of the example.

declare @val varchar(10) = 'abcd'

-- split the string apart
declare @len int = datalength(@val), @i int = 1

select cast(null as int) as id, cast(null as char(1)) as c
into   #temp
where  1=2

while @i <= @len
begin
  insert into #temp
  values (@i, substring(@val, @i, 1))

  set @i = @i + 1
end

-- pull the combinations (need to make this into dynamic SQL)
select a.c + b.c + c.c + d.c as names
into   #combos
from   #temp a, #temp b, #temp c, #temp d
where  a.id <> b.id and a.id <> c.id and a.id <> d.id and
                        b.id <> c.id and b.id <> d.id and
                                         c.id <> d.id

-- check work
select * from #combos

-- use the combos to pull records where the first name matches...
-- select * from [table] where firstname in (select names from #combos)

drop table #temp, #combos

Sorry -- don't have the time to figure out the dynamic SQL. It will be tricky to get it just right. Each additional character will add exponential bulk to the dyn-SQL.

This does not eliminate duplicate letters like in the name 'Suzanne'.