Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2008
    Posts
    53

    Unanswered: how can I make fulltext search

    I have table with columns:
    id
    name
    surname


    I would like to use fulltext search on column 'surname' so I should create fulltext index on column 'surname' ? But a full-text search key must be a unique and surname isn't unique so I can't make on this column fulltext index ? But I need fulltext search...

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    May I ask specifically what part of the full text functionality you need?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2008
    Posts
    53
    Something like that - for example I would like to find all people with surname contains 'obam' (example: Barack Obama):
    Code:
    SELECT *
    FROM People
    WHERE CONTAINS(Surname, 'obam')

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Is the ID column unique?

  5. #5
    Join Date
    Dec 2008
    Posts
    53
    yes - ID is unique and it is PK

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The key index has nothing to do with the column being indexed. It is only the way that SQL Server identifies which row belongs with which item in the index.

  7. #7
    Join Date
    Dec 2008
    Posts
    53
    ok thx - now I have fulltext index on columns name and surname. I would like to search records like 'Barack Obam' so:
    Code:
    select * from dbo.people where freetext((name, surname), '"Barack Obam"');
    but it returns 0 rows - I must write 'Barack Obama' (with 'a' at the end) and only then it returns records. But users often write not all words so what can I do ?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How many rows in the table? Can you live with:
    Code:
    ...surname LIKE '%obam%'
    Also, if you are worrying about users not typing the full word, what about mispellings?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I think you want something more along the lines of
    Code:
    select * 
    from dbo.people 
    where freetext((name), 'Barack')
      and freetext((surname), 'Obam')
    ;
    Freetext does not join columns together.

Posting Permissions

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