#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    London
    Posts
    166
    Rep Power
    12

    Searching Fields


    How do I search a particular field for a given string?
    For instance if I have a productDescription field, that contains a 30 word description of a particular Car for example, how would I search for the word RED or AIR CONDITIONING etc?

    Thanks for your help,
    Thanks in advance

    Chris
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Binghamton, NY
    Posts
    22
    Rep Power
    0
    Check out the Full Text Indexing engine. There is info in help, better info in the SQL server books available.

    In short the process goes something like this:

    1. create a primary key on your table.
    2. enable full text indexing on the database (sp_fulltext_database @action = 'enable'
    3. right click on the table and choose Full Text Index Table -> Define Full Text Index
    4. Follow the wizard
    5. right click on the catalog and choose Start Full Population

    When it's done, your query would be something like this:

    select * from PRODUCTS where contains(productDescription ,'car OR Air NEAR Conditioning')
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    London
    Posts
    166
    Rep Power
    12

    Is that the only way......?


    Is there no way of doing this with ASP and SQL. Its just that I'm currently doing a site that will start off using an access database and then be transformed to a SQL Server one as and when required. It would be much simpler if i could find a way of doing it that could be implemented on both? Access doesn't have a Full Text Indexing Engine does it?

    Thanks
    Thanks in advance

    Chris
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    not to my knowledge, no, Access doesn't have a Full Text Indexing Engine

    why not try the simple approach first:
    Code:
    select model
         , price
         , productDescription 
      from Cars
     where productDescription like '*RED*'
        or productDescription like '*AIR CONDITIONING*'
    use asterisks for the wildcard character in Access, use the percent sign in SQL Server

    search is case-insensitive in both databases
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo