SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Containstable wildcard exact match
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JohnSourcer
Starting Member

9 Posts

Posted - 07/02/2013 :  07:56:16  Show Profile  Reply with Quote
Hi Guru's,

I have the following SQL:

SELECT  Customers.CustomerId, Customers.Firstname, Customers.Surname FROM Customers INNER JOIN DealershipCustomers ON Customers.CustomerId = DealershipCustomers.CustomerId INNER JOIN Dealerships ON DealershipCustomers.DealershipId = Dealerships.DealershipId INNER JOIN CONTAINSTABLE(Customers, *, @Term) AS K ON Customers.CustomerId = K.[KEY]


I now want to pass a wildcard to @Term which works fine as say 'Joh*' but won't work when the term is 'John*' where 'John' is an exact match.

How do you get around this limitation?



JohnSourcer
Starting Member

9 Posts

Posted - 07/03/2013 :  03:01:42  Show Profile  Reply with Quote
Can this not be done? Seems a bit strange. Otherwise I'll have to pull out FT search altogether.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/03/2013 :  03:07:10  Show Profile  Reply with Quote
do you mean this?


SET @Tearm='"John*"'
SELECT  Customers.CustomerId, Customers.Firstname, Customers.Surname 
FROM Customers 
INNER JOIN DealershipCustomers 
ON Customers.CustomerId = DealershipCustomers.CustomerId 
INNER JOIN Dealerships 
ON DealershipCustomers.DealershipId = Dealerships.DealershipId 
INNER JOIN CONTAINSTABLE(Customers, *, @Term) AS K 
ON Customers.CustomerId = K.[KEY]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JohnSourcer
Starting Member

9 Posts

Posted - 07/03/2013 :  03:36:12  Show Profile  Reply with Quote
Yes, visakh.

If the match is exact i.e John, SQL throws an error but:

SET @Term='"Joh*"'


Returns the match.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/03/2013 :  03:44:34  Show Profile  Reply with Quote
quote:
Originally posted by JohnSourcer

Yes, visakh.

If the match is exact i.e John, SQL throws an error but:

SET @Term='"Joh*"'


Returns the match.


Ok...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JohnSourcer
Starting Member

9 Posts

Posted - 07/03/2013 :  05:11:27  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

Ok...




Sorry Visakh16, I possibly wasn't clear on this.

Here is a mockup of the issue:


A test table:

CREATE TABLE [dbo].[TestTable](
	[ID] [int] NOT NULL,
	[Code] [nvarchar](12) NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]





Add data:


INSERT INTO TestTable (ID, Code) VALUES (1, 'CB55FJGP')



Fulltext Index the table

Run this query:

DECLARE @Term nvarchar(10)
SET @Term='"CB55FJGP*"'

SELECT  TestTable.ID, TestTable.Code FROM  TestTable 
INNER JOIN CONTAINSTABLE(TestTable, *, @Term) AS K ON TestTable.ID = K.[KEY]	



Results in:

Syntax error near '"' in the full-text search condition '"CB55FJGP*'.

But changing the term to '"CB55FJG*"' works fine.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/03/2013 :  05:27:40  Show Profile  Reply with Quote
Ok. So I hope your issue is resolved

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JohnSourcer
Starting Member

9 Posts

Posted - 07/03/2013 :  05:37:04  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

Ok. So I hope your issue is resolved



lol

No it isn't. I know you're very knowledgeable. Can you explain why this doesn't work.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/03/2013 :  05:51:32  Show Profile  Reply with Quote
I'm not much familar with CONTAINSTABLE
see this similar discussion

http://stackoverflow.com/questions/6995220/containstable-wildcard-fails-on-phrase-match

and apply dmv to understand how its performing the search

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000