Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    70

    Unanswered: Is there a "CONTAINS" in SQL?

    I want to "match" by two columns. So something like:

    SELECT * FROM tablea A, tableb B WHERE B.msglist CONTAINS A.msg;



    A.msg looks like
    MSG1
    MSG2
    MSG3
    etc

    and B.msglist looks like
    (MSG1)&&(MSG3)&&(MSG9)
    (MSG1)&&(MSG2)&&(MSG6)
    (MSG8)&&(MSG7)&&(MSG3)
    etc


    is there any way to do that?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    String handling isn't very consistent between different versions of SQL. The exact syntax will depend on what SQL engine you are using, but it is definitely possible.

    If you can settle for a "loose match" that might be confused by special characters like _ and %, you could use concatenate percent signs at the begining and end of your "search for" column, and use that as the LIKE pattern. This is reasonably portable, but it still depends on the string concatenation syntax (which varies from one SQL engine to another).

    -PatP

  3. #3
    Join Date
    Jan 2004
    Posts
    70
    Thanks. I'm on DB2

    So something like:

    SELECT * FROM tablea A, tableb B WHERE B.msglist LIKE % + A.msg + %;

    I dont even know how to concatenate strings in db2 =\

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm moving this thread to the DB2 forum, since you'll get a lot more relevant comments there than in the "pure" SQL forum.

    I'd use:
    Code:
    SELECT *
       FROM tablea A, tableb B
       WHERE B.msglist LIKE '%' CONCAT A.msg CONCAT '%';
    -PatP

  5. #5
    Join Date
    Dec 2005
    Posts
    273
    I'm afraid, you cannot use a table column at the right side of a LIKE. ( at least in DB2 for z/OS you can't )

    but a

    ... WHERE LOCATE(A.msg , b.msglist ) > 0

    should work.
    LOCATE returns the position of parameter1 in parameter2 if found, and zero if parameter2 does not contain parameter1

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by umayer
    I'm afraid, you cannot use a table column at the right side of a LIKE. ( at least in DB2 for z/OS you can't )
    Really? I thought expressions as the LIKE pattern were supported in DB2 7.2 and later across all platforms. Gee, I learned something, so I can go home now!

    -PatP

  7. #7
    Join Date
    Dec 2005
    Posts
    273
    LIKE itself is supported in DB2 z/OS, of course.

    WHERE column-name LIKE expression

    but expression must be:
    a special register or
    a host-variable or
    a string-constant or
    a result of a function based on the above.

    expression must not be (or contain) a column-name

Posting Permissions

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