Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: LIKE operator with CONCAT

    DB2 version 8.1.3.132 in Win XP.

    I'm trying to use the LIKE operator and the CONCAT operator in the same sentence. Although it works fine when concatenating two strings, it fails when concatenating a VARCHAR column and a string.

    Something like this:
    Code:
    SELECT 
      MOVIE.TITLE
    FROM
      MOVIE
    WHERE
      MOVIE.TITLE LIKE MOVIE.TITLE2 || 'My beautyful string'
    I get the DBA2191E error

    Have any idea?

    Thank you in advance.

    ..:: Carlos ::..

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    see if the content of this thread is of any help

    http://www.dbforums.com/t1118136.html
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    well, test this:

    with temp1 as (
    SELECT
    MOVIE.ID,
    MOVIE.TITLE LIKE MOVIE.TITLE2 || 'My beautyful string' newField
    FROM
    MOVIE
    )
    SELECT
    MOVIE.TITLE
    FROM
    MOVIE
    on temp.id = movie.id
    WHERE
    MOVIE.TITLE LIKE temp1.newField

  4. #4
    Join Date
    Jan 2005
    Posts
    191
    LIKE operators normally use underscores and percent signs on the right hand side. I'm not sure where these are in your query. However you might be looking for something like
    where posstr(MOVIE.TITLE, MOVIE.TITLE2 || 'My beautyful string') > 0

    James Campbell

  5. #5
    Join Date
    Aug 2004
    Posts
    2
    Thank you all,

    It seems that I cannot make working the solution provided by jacampbell, and the one by achiola is too complicated.

    The problem with this kind of queries is that they are dynamically generated, according to some users entry.

    What I think I will do is to solve this part of the querie in Java, by using the "startsWith" method.

    ..:: Carlos ::..

  6. #6
    Join Date
    Feb 2005
    Location
    Puerto Rico
    Posts
    16

    Cool to char and %

    try this in the statement you have

    ...char(MOVIE.TITLE2) || char('My beautyful string')

    if you are using the LIKE operand you should also include the %.

    ...char(MOVIE.TITLE2) || char('My beautyful string%')

    Try and let me know...

    J.C.

Posting Permissions

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