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
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Add this one row:
serialnumber of 00000A00J010002158
Create full text index with that field.
DECLARE @searchTerm AS nvarchar(100)
SET @searchTerm = '00000A00J010002158'
WHERE CONTAINS ( [serialnumber], @searchTerm)
o row(s) returned
set @searchTerm = 'A00J010002158'
SELECT RANK, serialnumber FROM [search] srch
--CONTAINSTABLE ([search , *, @searchTerm) AS ACC
containstable ([search] , *, 'A00J010002158') AS ACC
ON srch.ID = ACC.
1 rows(s) returned
- This topic was modified 1 month ago by Sailor.