full text search

  • Sailor

    SSCertifiable

    Points: 5214

    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 1 month ago by  Sailor.
  • Site Owners

    SSC Guru

    Points: 80385

    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

    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: 5214

    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: 994238

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • David Burrows

    SSC Guru

    Points: 64471

    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.

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

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