Results 1 to 10 of 10
  1. #1
    Join Date
    May 2010
    Posts
    1

    Unanswered: Question about LIKE operator

    I have a problem in my project.

    I have to retrive all data which start with 11. (for example 11,112,1156)
    but the provided data type of this column is INTEGER.

    I tried to use LIKE but it didn't work.

    Can anyone suggest me how to solve this problem?

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    Try:

    ... WHERE VARCHAR(your_column) LIKE '11%'

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what about leading 0's when converting datatype? you'll need to strip them out as well.
    Dave

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by dav1mo View Post
    what about leading 0's when converting datatype? you'll need to strip them out as well.
    Dave
    You don't need to worry about leading 0's if you are using the VARCHAR function.

    Lenny

  5. #5
    Join Date
    Apr 2009
    Posts
    42
    Quote Originally Posted by Lenny77 View Post
    You don't need to worry about leading 0's if you are using the VARCHAR function.

    Lenny
    As far as I know, there is not existing a VARCHAR function that accept integer type.

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    wilsonfv, it depends and the version of DB2.

    DB2 LUW V8.2 and 9.5, VARCHAR function does not accept integer as a parameter.

    DB2 LUW V9.7, DB2 z/OS V8, and V9, VARCHAR funcion does accept integer as a parameter.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CHAR(integer-expression) is accepted more widely,
    including DB2 LUW V8.2 to 9.7, DB2 z/OS V7 to V9, DB2 iSeries V5R2 to V6R1.

    CHAR(integer-expression) would be padded with trailing blanks.
    But, it would be no matter using in

    ... WHERE CHAR(your_column) LIKE '11%'
    Last edited by tonkuma; 05-10-10 at 17:33.

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question Performace

    Quote Originally Posted by tonkuma View Post
    CHAR(integer-expression) is accepted more widely,
    including DB2 LUW V8.2 to 9.7, DB2 z/OS V7 to V9, DB2 iSeries V5R2 to V6R1.

    CHAR(integer-expression) would be padded with trailing blanks.
    But, it would be no matter using in

    ... WHERE CHAR(your_column) LIKE '11%'
    Agree with you, tonkuma ! We can use Char as well as Varchar.

    But if your_column is first column of index using function can affect performance.

    Lenny

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation Performance

    Instead of LIKE expression you can use the query with much better performance:

    Code:
    with power(pwr) as
    (select 1 from sysibm.sysdummy1
     union all
    select pwr + 1
      from power
    where pwr <= 7) 
    select * from 
    your_table join power
    On 
    your_column between 11 * power(10, pwr) and 12 * power(10, pwr + 1) - 1
    or your_column  = 11
    Lenny

  10. #10
    Join Date
    Jul 2009
    Posts
    150

    Exclamation Correction to Lenny's query

    Quote Originally Posted by Lenny77 View Post
    Instead of LIKE expression you can use the query with much better performance:

    Code:
    with power(pwr) as
    (select 1 from sysibm.sysdummy1
     union all
    select pwr + 1
      from power
    where pwr <= 7) 
    select * from 
    your_table join power
    On 
    your_column between 11 * power(10, pwr) and 12 * power(10, pwr + 1) - 1
    or your_column  = 11
    Lenny
    Lenny, your query consists the small mistake. Let me correct it:

    Code:
    with power(pwr) as
    (select 0 from sysibm.sysdummy1
     union all
    select pwr + 1
      from power
    where pwr <= 7
    )
    select * from 
    your_table join power
    On 
    your_column between 11 * power(10, pwr) and 12 * power(10, pwr) - 1
    Thanks, Kara.

Posting Permissions

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