Searching for name that can have 1, 2 or 3 parts

[Clanger67]

I am trying to create a query that will return a persons name from a table.

The table contains a field called full_name.

I have created a full text index on the table on that field.

I have the following query

ALTER procedure [dbo].[spGetContactFromContactName]
@contactname VARCHAR(200)
AS
DECLARE @searchstr VARCHAR(200)
set @searchstr = '"'+REPLACE(@contactname,',','*" OR ''')+'*"'
select * from tbl_ContactDetails
where contains(Full_Name,@searchstr)

This works, but only in certain circumstances.

If the field contains the name John James Jones the query works if you enter any of the following:
[ul]
[li]John[/li]
[li]James[/li]
[li]Jones[/li]
[li]John James[/li]
[li]James Jones[/li]
[li]John James Jones[/li]
[/ul]
but it doesn't work if you enter John Jones.

I have searched online for a solution, which is where the above query came from. Any ideas or pointers would be much appreciated.

Thanks in advance

David

 

[markros]

Your thread is answered in MSDN forum

http://social.msdn.microsoft.com/Fo...l/thread/0c0d0859-6e02-473c-8a17-bfcbd71d2b65

PluralSight Learning Library

 

[Clanger67]

I have checked that thread, but when trying the suggested code it throws an error

 

[markros]

I am glad you figured this out. Just curious, which other forum helped most? Was is SQL Central?

PluralSight Learning Library

 

[simian336]

you could do it with dynamic sql...

declare @test as varchar(100)
declare @sql as varchar(100)

set @test = 'John James Jones'

set @sql ='select * from test where yourfield ' + 'like ''%' + replace(rtrim(@test), ' ', '%'' or yourfield like ''%') + '%'''

print @sql
exec (@sql)

Simi