Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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
52326 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
52326 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  
 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.13 seconds. Powered By: Snitz Forums 2000