full text search

Question

Post reply

full text search

Sailor

SSCertifiable

Points: 5246
More actions
July 18, 2019 at 12:21 pm

#3664112
Trying to understand why when I search for numerics in full text, contains does not return, but containstable does. It seems that in that in this instance the 0's are repeatable and not indexed, but why does one work and the other not? Is there a way to make it work regardless?
CREATE TABLE [dbo].[EquipSrch](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SERIAL_NUMBER] [nvarchar](18) NOT NULL,
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]
GO
Add this one row:
serialnumber of 00000A00J010002158
Create full text index with that field.

DECLARE @searchTerm AS nvarchar(100)
SET @searchTerm = '00000A00J010002158'
SELECT *
FROM [Search]
WHERE CONTAINS ( [serialnumber], @searchTerm)
o row(s) returned
set @searchTerm = 'A00J010002158'
SELECT RANK, serialnumber FROM [search] srch
INNER JOIN
--CONTAINSTABLE ([search , *, @searchTerm) AS ACC
containstable ([search] , *, 'A00J010002158') AS ACC
ON srch.ID = ACC.
1 rows(s) returned
- This topic was modified 5 years, 5 months ago by Sailor.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

mohan.muralid SSC Journeyman Points: 99 More actions · Answer 2

Hello,

You have to define the full-text index on the table. After that contains will work. For defining the full-text index, right-click on the table and select full-text index and click on define full-text index and follow the steps to define.

Later you can run the below query

DECLARE @searchTerm AS nvarchar(100)

SET @searchTerm = '00000A00J010002158'

SELECT * FROM [Search] WHERE CONTAINS ( [serialnumber], @searchTerm)

--------------------------- else --------------

SELECT * FROM [Search] WHERE CONTAINS ( [serialnumber], '00000A00J010002158')

Sailor SSCertifiable Points: 5246 More actions · Answer 3

Ahh, yes the table does have a full text definition. It's just not finding A00J010002158. If I do like '%A00J010002158', it finds it. But that is very slow.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

Sailor wrote:

Ahh, yes the table does have a full text definition. It's just not finding A00J010002158. If I do like '%A00J010002158', it finds it. But that is very slow.

It doesn't matter if it's FTS or not... searches based on leading wild cards are going to cause a full table or index scan. Period. If what you're searching for contains leading zeros, either remove them from the Full Text or search for the term with the leading zeros.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65143 More actions · Answer 5

Jeff Moden wrote:

Sailor wrote:
Ahh, yes the table does have a full text definition. It's just not finding A00J010002158. If I do like '%A00J010002158', it finds it. But that is very slow.
It doesn't matter if it's FTS or not... searches based on leading wild cards are going to cause a full table or index scan. Period. If what you're searching for contains leading zeros, either remove them from the Full Text or search for the term with the leading zeros.

+1

Sometimes I add an additional column with altered (ie removing leading zeros) / additional data to aid FTS searching.

As a sidebar, if not using FTS with partial matching of trailing text I use a persisted computed column with a suitable index and either fixed number of tail characters to match on or reverse the whole column and use LIKE 'XXXX%'

Far away is close at hand in the images of elsewhere.
Anon.