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,418
    Provided Answers: 7
    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

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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,434
    Provided Answers: 10
    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
  •