CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
MAK
MAK

Reputation: 7260

Search words in the string to column value with soundex check

I have the following sample data:

create table tbl_test
(
    name varchar(50)
);

insert into tbl_test values('Frapples Bob'),('A Molive Paul'),('James Make'),('Mak Lee'),('James Harry');

Note: Actual table have more than 100 millions of records.

Here I want to search for name as given below:

Search for the name: Mak Jams

Expected result: The result should display if both keywords present in the name field with soundex check.

name
-------
James Make

My try 1:

declare @str varchar(100) = 'Mak Jams'

declare @where varchar(max) = ''

declare @query varchar(max) = ''

select @where = replace(STUFF((SELECT 'CHARINDEX('''+ item +''',name) > 0 AND '
                from dbo.f_split(@str,'')
                 FOR XML PATH('')
        ), 1, 0, ''),'>','>')

set @where = substring(@where,1,(len(@where) - 3))            
print(@where);

set @query = 'select name 
              from tbl_test 
              where '+@where+'';
print(@query);
exec(@query);

Output: Unable to the expected result.

Try 2: I tried to add soundex for both sides (value and column) but soundex works only for the first keyword in the string.

select name 
from tbl_test   
where CHARINDEX(soundex('Mak'),soundex(name)) > 0 AND CHARINDEX(soundex('Jams'),soundex(name)) > 0

Output: Unable to the expected result.

Upvotes: 1

Views: 109

Answers (1)

Nitika
Nitika

Reputation: 463

You can achieve expected result from below query

    select * from (
    select  
    name,
      Reverse(ParseName(Replace(Reverse(name), ' ', '.'), 1)) As [M1]
     , Reverse(ParseName(Replace(Reverse(name), ' ', '.'), 2)) As [M2]
     , Reverse(ParseName(Replace(Reverse(name), ' ', '.'), 3)) As [M3]
     ,SOUNDEX('mak') mak,SOUNDEX('jams') jams,
     SOUNDEX( Reverse(ParseName(Replace(Reverse(name), ' ', '.'), 1)) )As [M11]
     ,SOUNDEX(  Reverse(ParseName(Replace(Reverse(name), ' ', '.'), 2))) As [M21]
     , SOUNDEX( Reverse(ParseName(Replace(Reverse(name), ' ', '.'), 3)))m31
    from tbl_test )a

    where  (mak=[M11] or mak=[m21] or mak=[M31]) and (jams=[M11] or jams=[m21] or 
           jams=[M31])

Simply compare their soundex value with search keyword soundex.You have to split both string 1. Parameter string and table column string for expected output.

Upvotes: 1

Related Questions