Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2008
    Posts
    12

    Unanswered: Usage of LTRIM and RTRIM

    Hi,

    I have to search in a table for a specific detail name for that I have written the following query.

    SELECT * FROM MA1.T5420_COMM_DETAIL WHERE COMMUN_DTL_NM='Optima Credit Card ';

    Whnever I am enetring the value of commun_dtl_nm without those extra spaces I am not able to search that name in the database.

    I have tried following as well

    SELECT * FROM MA1.T5420_comm_detail where
    LTRIM(RTRIM(COMMUN_DTL_NM))=
    RTRIM(LTRIM('Optima Credit Card ')) WITH UR;


    In this case also I was not able to get any matching rows when ever I was entering the commun_dtl_nm without ane extra spaces.


    Please help me to achiev this.

    Thanks
    Arun

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    have you tried where
    COMMUN_DTL_NM like 'Optima Credit Card%';
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Mar 2008
    Posts
    12

    Usage of LTRIM and RTRIM

    Hi,

    Thanks for the quick reply.

    I would have used it, but my requirenment is to match the exact string rather than matching a similar string.

    Thanks
    Arun

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What's the exact definition of your table "MA1.T5420_comm_detail"? Maybe the column in question is defined as CHAR and VARCHAR would be better?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Mar 2008
    Posts
    12

    Usage of LTRIM and RTRIM

    Column is of varchar(80) but the velue which has been enetered in the table is of exact 80 length with spaces.

  6. #6
    Join Date
    Mar 2008
    Posts
    12

    Usage of LTRIM and RTRIM

    Column is of varchar(80) but the velue which has been enetered in the table is of exact 80 length with spaces.

    I am not able to comare the values, if I am taking only text without spaces.

    Thanks
    Arun

  7. #7
    Join Date
    Mar 2008
    Posts
    12

    Usage of LTRIM and RTRIM

    Column is of varchar(80) but the velue which has been enetered in the table is of exact 80 length with spaces.

    I am not able to comare the values, if I am taking only text without spaces.

    Thanks
    Arun

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Maybe I'm missing something, but if you have the trailing spaces in the value stored in the table, it is mandatory that DB2 takes those spaces into consideration during string comparison. After all, those spaces are part of the string value and you can't expect that a comparison only considers a part of the value.

    As for the 2nd query, could you shown us the result from an "SELECT '@' || COMMUN_DTL_NM || '@' FROM MA1.T5420_comm_detail"? I'm asking because you may not have exactly the value "Optima Credit Card" in your table. Maybe you assume a case-insensitive string comparison, too?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Jun 2006
    Posts
    471
    trailing spaces are not taken into account
    where col='xxxx ' is the same as col='xxxx '
    you can put as many spaces as you want- only 1 is taken into account
    look at the sample
    [09:34 AM][db2admin]/cygdrive/c/workdir:db2 "select * from var where col1='xxx '" <-- with 1 space
    COL1
    ----------
    xxx
    xxx

    2 record(s) selected.

    [09:34 AM][db2admin]/cygdrive/c/workdir:db2 "select * from var where col1='xxx '" <-- with 2 spaces
    COL1
    ----------
    xxx
    xxx

    2 record(s) selected.

    [09:34 AM][db2admin]/cygdrive/c/workdir:db2 "select col1,hex(col1) from var"

    COL1 2
    ---------- --------------------
    xxx 78787820
    xxx 787878202020

    2 record(s) selected.
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I stand corrected. Here is what the DB2 manual (http://publib.boulder.ibm.com/infoce.../r0008479.html
    says:
    When comparing character strings of unequal lengths, the comparison is made using a logical copy of the shorter string, which is padded on the right with blanks sufficient to extend its length to that of the longer string. This logical extension is done for all character strings, including those tagged as FOR BIT DATA.
    So I would say that the OP has a different problem, and it would be good to get the answer to my 2nd question.
    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
  •