FullText Search - SQL Server
+ Reply to Thread
Page 3 of 5 FirstFirst 1 2 3 4 5 LastLast
Posts 21 to 30 of 48
  1. #21

    Potty-trained

    suityou01's Avatar
    Join Date
    Jan 2009
    Location
    Skid row
    Posts
    20,969
    Thanks (Given)
    177
    Thanks (Received)
    211
    Likes (Given)
    745
    Likes (Received)
    715

    Default

    Quote Originally Posted by LondonManc View Post
    In summary then, CONTAINS(YourColumn,'Att') doesn't work but CHARINDEX('Att', Yourcolumn) >0 gets results?

    You've created the full-text catalog?

    You've created the full-text index on the table and correctly associated it with the catalog above?

    Have you tried doing it by using the wizard in SSMS?
    Your summary is perfectly correct.

    I tried dropping the index and the catalog and re-creating using the GUI. Still no joy.
    Brillo invited me back.

  2. #22

    Godlike


    Join Date
    Sep 2014
    Location
    Non-Event Horizon
    Posts
    8,678
    Thanks (Given)
    334
    Thanks (Received)
    587
    Likes (Given)
    2210
    Likes (Received)
    2630

    Default

    Quote Originally Posted by suityou01 View Post
    Your summary is perfectly correct.

    I tried dropping the index and the catalog and re-creating using the GUI. Still no joy.
    I won't have access to SQL Server until weekend, so I guess we'll have to keep an idiot in suspense to see if I can recreate it.
    The greatest trick the devil ever pulled was convincing the world that he didn't exist

  3. #23

    Fingers like lightning


    Join Date
    Aug 2015
    Posts
    573
    Thanks (Given)
    57
    Thanks (Received)
    65
    Likes (Given)
    294
    Likes (Received)
    260

    Default

    Quote Originally Posted by suityou01 View Post
    Hello, can you help me as I am too stupid to figure this out for myself.

    I want to enable FullText search on a Text column. I have added a FullText catalog, and added the column to the catalog and populated the index.
    The FullText search service is running.

    However I get no hits. I should point out that StopWords is off.

    Soooo

    If my column contains 'Attrn' and I search on CONTAINS (MyColumn,'Att') I would expect to get a hit, but I don't.

    Are there any other bear traps I haven't considered?

    FullText is doing a language aware search.

    CONTAINS(MyColumn, 'Att') is looking for the word Att - in this case your word is Attrn so it's not finding a match

    Try

    CONTAINS (MyColumn, '"Att*"' )

  4. #24

    Fingers like lightning


    Join Date
    Aug 2015
    Posts
    573
    Thanks (Given)
    57
    Thanks (Received)
    65
    Likes (Given)
    294
    Likes (Received)
    260

    Default

    Quote Originally Posted by LondonManc View Post
    Att with CONTAINS or FREETEXT should turn up the row where a correctly indexed column contains Att (no wildcards needed).
    Contains the word Att - I think that's where this is failing. It's not doing a string match, it's doing a word match - you need the wildcard for a string match.

  5. #25

    Godlike


    Join Date
    Sep 2014
    Location
    Non-Event Horizon
    Posts
    8,678
    Thanks (Given)
    334
    Thanks (Received)
    587
    Likes (Given)
    2210
    Likes (Received)
    2630

    Default

    Quote Originally Posted by teapot418 View Post
    FullText is doing a language aware search.

    CONTAINS(MyColumn, 'Att') is looking for the word Att - in this case your word is Attrn so it's not finding a match

    Try

    CONTAINS (MyColumn, '"Att*"' )
    Yep, I think this is the key - "Attrn" is not recognised as a logical linguistic extension of "Att". I finally cheated and RTFM
    The greatest trick the devil ever pulled was convincing the world that he didn't exist

  6. #26

    Double Godlike!


    Join Date
    Dec 2012
    Posts
    10,384
    Thanks (Given)
    755
    Thanks (Received)
    742
    Likes (Given)
    4942
    Likes (Received)
    2860

    Default

    Quote Originally Posted by LondonManc View Post
    Yep, I think this is the key - "Attrn" is not recognised as a logical linguistic extension of "Att". I finally cheated and RTFM

    And you message to Suity is?

  7. #27

    Godlike


    Join Date
    Sep 2014
    Location
    Non-Event Horizon
    Posts
    8,678
    Thanks (Given)
    334
    Thanks (Received)
    587
    Likes (Given)
    2210
    Likes (Received)
    2630

    Default

    Quote Originally Posted by MrMarkyMark View Post
    And you message to Suity is?
    If he'd have RTFM'd yesterday, he wouldn't have need to post.

    SQL Server BooksOnLine is an excellent resource in itself, without the countless blogs out there.
    The greatest trick the devil ever pulled was convincing the world that he didn't exist

  8. #28

    Godlike

    original PM's Avatar
    Join Date
    Apr 2008
    Location
    Cheshire
    Posts
    8,828
    Thanks (Given)
    22
    Thanks (Received)
    279
    Likes (Given)
    72
    Likes (Received)
    1252

    Default

    surely just

    where columnname like '%textyouarelookingfor%'

    works fine too?

    or am I missing something?

  9. #29

    Godlike


    Join Date
    Sep 2014
    Location
    Non-Event Horizon
    Posts
    8,678
    Thanks (Given)
    334
    Thanks (Received)
    587
    Likes (Given)
    2210
    Likes (Received)
    2630

    Default

    Quote Originally Posted by original PM View Post
    surely just

    where columnname like '%textyouarelookingfor%'

    works fine too?

    or am I missing something?
    full text index offers better performance. Wildcards kill off the advantages that you get (a bit like adding a function to a join when joining a date to a datekey integer for example).
    The greatest trick the devil ever pulled was convincing the world that he didn't exist

  10. #30

    Potty-trained

    suityou01's Avatar
    Join Date
    Jan 2009
    Location
    Skid row
    Posts
    20,969
    Thanks (Given)
    177
    Thanks (Received)
    211
    Likes (Given)
    745
    Likes (Received)
    715

    Default

    Quote Originally Posted by MrMarkyMark View Post
    You mean apart from the fact that code is wrong......

    How do you get leading wildcard full-text searches to work in SQL Server? - Stack Overflow

    My invoice is in the post
    Quote Originally Posted by suityou01 View Post
    You lost me. How is my code wrong?
    Quote Originally Posted by MrMarkyMark View Post
    No Wildcard
    Quote Originally Posted by suityou01 View Post
    You don't always need a wild card. I understand this would force a full table scan, which kind of negates the effect of going to all the trouble of creating a full text index. Contains should match on the string wherever your search string occurs.
    Quote Originally Posted by LondonManc View Post
    Yep, I think this is the key - "Attrn" is not recognised as a logical linguistic extension of "Att". I finally cheated and RTFM
    Quote Originally Posted by MrMarkyMark View Post
    And you message to Suity is?
    Quote Originally Posted by teapot418 View Post
    Contains the word Att - I think that's where this is failing. It's not doing a string match, it's doing a word match - you need the wildcard for a string match.
    Quote Originally Posted by teapot418 View Post
    FullText is doing a language aware search.

    CONTAINS(MyColumn, 'Att') is looking for the word Att - in this case your word is Attrn so it's not finding a match

    Try

    CONTAINS (MyColumn, '"Att*"' )
    Brillo invited me back.

+ Reply to Thread
Page 3 of 5 FirstFirst 1 2 3 4 5 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Content Relevant URLs by vBSEO 3.6.0 ©2011, Crawlability, Inc.