Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Question Unanswered: SQL for a search

    hi,

    i'm fairly new to SQL and i'm trying to do a web-based search page which takes 2 or more parameters and checks if they are present in any of 4 or 5 fields inside a certain table.

    i've created some SQL but its pretty mess and i'm sure not ver efficient... can anyone let me know the best way of doing this?

    thanks in advance.

    dog


    current dodgy code :
    SELECT * FROM tblProjects WHERE project_type1 IN ('varSearch1', 'varSearch2') OR project_type2 IN ('varSearch1', 'varSearch2') OR project_type3 IN ('varSearch1', 'varSearch2') OR project_brief like '%varSearch1%' OR project_brief like '%varSearch2%' OR project_comments like '%varSearch1%' OR project_comments like '%varSearch2%' OR project_title like '%varSearch1%' OR project_title like '%varSearch2%'

  2. #2
    Join Date
    May 2003
    Posts
    87

    Re: SQL for a search

    How about this sql?

    Code:
    select * 
    from tblProjects
    where project_type1 || project_type2 || project_type3 || project_type4 || project_type5 like '%varSearch1%';

  3. #3
    Join Date
    Oct 2003
    Posts
    2
    thanks for your reply.....

    i'm fairly new to SQL and wasn't aware you could use the || ..... are these used the same as commas? anyway, i get an error when i use them so maybe its not what you meant :-)

    my second problem is that the SQL example you gave searches only on one parameter. how would i do something similar but with 2 parameters?

    thanks again for your help.

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Originally posted by dog
    thanks for your reply.....

    i'm fairly new to SQL and wasn't aware you could use the || ..... are these used the same as commas? anyway, i get an error when i use them so maybe its not what you meant :-)

    my second problem is that the SQL example you gave searches only on one parameter. how would i do something similar but with 2 parameters?

    thanks again for your help.
    Just a warning when concatenating strings. I got this from the Oracle documentation...

    <<<ORACLE DOCUMENTATION>>>
    Although Oracle treats zero-length character strings as nulls, concatenating a zero-length character string with another operand always results in the other operand, so null can result only from the concatenation of two null strings. However, this may not continue to be true in future versions of Oracle. To concatenate an expression that might be null, use the NVL function to explicitly convert the expression to a zero-length string
    <<<END DOCUMENTATION>>>

Posting Permissions

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