Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    6

    Unanswered: SQL statement - how to 'chop' phrase

    hi guys how do i do this SQL statements when a user typed in search text field in my ASP.net webpage? How do I 'chop' the typed-in phrases?

    examples:

    1. typed in phrase: marketing assistant jobs

    the resulting SQL statement should be:

    SELECT field1,field2
    FROM myTable
    WHERE CONTAINS(*, 'marketing AND assistant AND jobs')

    2. typed in phrase: public administration

    the resulting SQL would be:

    SELECT field1,field2
    FROM myTable
    WHERE CONTAINS(*, 'public AND administration')

    3. typed in phrase: SQL server full text search
    result would be:

    SELECT field1,field2
    FROM myTable
    WHERE CONTAINS(*, 'SQL AND server AND full AND text AND search')



    thanks for any ideas.

    .

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This actually need sto be done before SQL Server even sees the data. Each of the .NET languages has diffent string handling functions, so there isn't a "one size fits all" solution.

    I'll move this thread to the ASP forum for you. It will probably get better answers there.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,476
    Provided Answers: 11
    What i Done done is join all the feilds you want to search in to make a New Feild

    then use the WHERE


    Whereis = "WHERE newsearchfeild Like '%" & Searchingfor & "%'"
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,445
    Provided Answers: 12
    It seems that replacing spaces (" ") with " AND " would give you a pretty good start...
    Code:
    --Initialise parameter
    DECLARE @parameter varchar(100)
    SET @parameter = 'marketing assistant jobs'
    
    --Perform the replace
    SET @parameter = Replace(@parameter, ' ', ' AND ')
    
    --And then search...
    SELECT field1,field2
    FROM myTable
    WHERE CONTAINS(*, @parameter)
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2009
    Posts
    6
    Quote Originally Posted by gvee View Post
    It seems that replacing spaces (" ") with " AND " would give you a pretty good start...
    Code:
    --Initialise parameter
    DECLARE @parameter varchar(100)
    SET @parameter = 'marketing assistant jobs'
    
    --Perform the replace
    SET @parameter = Replace(@parameter, ' ', ' AND ')
    
    --And then search...
    SELECT field1,field2
    FROM myTable
    WHERE CONTAINS(*, @parameter)
    hi gvee, thanks for the suggestion, it's working when you are searching words or phrases without 'AND' and 'OR' but when i have search phrases with 'AND' or 'OR' for example 'sun and moon' or 'my way or highway' it gives error. i searched to resolve this issue but i'm stuck. any ideas?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,445
    Provided Answers: 12
    Why not remove/replace the "and"s from the initial string before replacing the spaces?
    Code:
    SET @parameter = Replace(@parameter, 'and', '&')
    SET @parameter = Replace(@parameter, ' ', ' AND ')
    George
    Home | Blog

Posting Permissions

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