Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2010
    Posts
    2

    Unanswered: Like operator in DB2

    Hello,

    I am new to this forums and new to DB2 too.

    I need some help regarding the LIKE operator in DB2.

    Here is my requirement.
    I have a table that has values like:
    1234
    12345
    12345AB
    1234AB
    AB1234CD
    1234ABCD

    I am supposed to write a query that looks for '1234' and return these values:
    1234
    1234AB
    AB1234CD
    1234ABCD

    Any help is greatly appreciated.

    Thanks,
    Nikhil

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by nikhilk View Post
    12345
    12345AB
    Nikhil
    Why do these two rows not qualify?

    Andy

  3. #3
    Join Date
    Jan 2010
    Posts
    2
    Andy,

    I am looking for row values that has the number '1234' in it along with alphabets but not numbers, at the beginning or at the end.

    Nikhil.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Something along these lines:
    Code:
    select 
    ..
    where  int(trim(translate(yourcolumn,'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'))) = 1234
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Nick, out of curiosity have you ever evaluated what it does to performance using translate? I have never had to use it.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Jan 2010
    Location
    Atlanta, GA
    Posts
    2
    Why aren't you able to do:

    select <column_name> from <table_name> where <column_name> like '&#37;1234%';

    Maybe I'm missing something.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Nikhil's replay to Andy's question:

    I am looking for row values that has the number '1234' in it along with alphabets but not numbers, at the beginning or at the end.
    The 4 numbers have to be can NOT have another number immediately before or after it. It has to be nothing or an alpha character.

    Using %1234% would return 12345 and 12345AB and both of these violate the restriction.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What's with rows like 0ABC1234def567? If such a row should qualify, the TRANSLATE approach won't work either.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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