Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    Join Date
    Apr 2003
    Location
    Charlotte, NC
    Posts
    3

    Unanswered: db2-- Decimal to Varchar without leading zeros

    I am trying to cast a Decimal datatype to Varchar using
    SELECT cast(char (OUTSTANDINGBAL) as varchar (30))
    FROM CREDITTable;

    and i get the results as

    000000000000012.250

    Decimal data type is defined as decimal(18,3) on the CREDITTable.

    I need to avoid the leading zeros and get the 12.250 to insert into anothe table.

    Is there any way I can do this?
    I looked through most of the functions db2 has but it didn't get me anywhere.

    I would really apprecaite it if you could forward any examples...

    thanks
    faris

  2. #2
    Join Date
    Dec 2002
    Posts
    134

    Re: db2-- Decimal to Varchar without leading zeros

    One of the ways is char and rtrim combo
    values rtrim( char ( 1.2345 ) )

    You need rtrim to remove trailing spaces

  3. #3
    Join Date
    Apr 2003
    Location
    Charlotte, NC
    Posts
    3

    Re: db2-- Decimal to Varchar without leading zeros

    Originally posted by chuzhoi
    One of the ways is char and rtrim combo
    values rtrim( char ( 1.2345 ) )

    You need rtrim to remove trailing spaces
    Well.. you can't use rtrim on decimal... so that won't work. when I tranfer Decimal to character it will have the leading zeros and does not have trainling spaces to remove.. so i end up with leading zeros..

  4. #4
    Join Date
    Dec 2002
    Posts
    134

    Re: db2-- Decimal to Varchar without leading zeros

    Probably I do not understand something, but I said you should use 2 functions char AND rtrim
    First will convert to character with possible trailing spaces, the second will remove them

    regards,
    dmitri

  5. #5
    Join Date
    Apr 2003
    Posts
    191

    Re: db2-- Decimal to Varchar without leading zeros

    Hi fhussain,

    how about something along these lines:

    select

    substr(cast( 1000*OUTSTANDINGBAL as bigint ) , 1, length( cast( 1000*OUTSTANDINGBAL as bigint ) -3) || ',' ||
    substr(cast( 1000*OUTSTANDINGBAL as bigint ) , length( cast( 1000*OUTSTANDINGBAL as bigint ) -2, 3)

    from CREDITTable

    Didn't check for syntax, but you may get the idea anyway.

    Johann


    Originally posted by fhussain
    I am trying to cast a Decimal datatype to Varchar using
    SELECT cast(char (OUTSTANDINGBAL) as varchar (30))
    FROM CREDITTable;

    and i get the results as

    000000000000012.250

    Decimal data type is defined as decimal(18,3) on the CREDITTable.

    I need to avoid the leading zeros and get the 12.250 to insert into anothe table.

    Is there any way I can do this?
    I looked through most of the functions db2 has but it didn't get me anywhere.

    I would really apprecaite it if you could forward any examples...

    thanks
    faris

  6. #6
    Join Date
    Apr 2003
    Location
    Charlotte, NC
    Posts
    3

    Re: db2-- Decimal to Varchar without leading zeros

    Originally posted by chuzhoi
    Probably I do not understand something, but I said you should use 2 functions char AND rtrim
    First will convert to character with possible trailing spaces, the second will remove them

    regards,
    dmitri
    When you convert it to char it will have leading zeros infront of the decimal. So, using the trim will not remove those zeros cos it wud not have any trailing space. when you convert decimall to character you will not get trailing spaces instead you will get zeros in form of the number.

    Fora example say you specify deciaml (18,3) and hold a value of 12.5.
    when you change this to char you will have 0000012.500 somthing like that.
    So, i need to emove those leading zeros.

  7. #7
    Join Date
    Jun 2007
    Posts
    3

    tested solution

    Hi
    You can use the following expression to remove the leading/trailing spaces resulting from decimal to varchar conversion.

    replace(replace(rtrim(ltrim(replace(char(<FIELD_NAME>), '0', ' '))), ' ', '0'), '.', '')

    Regards
    Ankur Jain

    http://ankurjain.org

  8. #8
    Join Date
    Dec 2005
    Posts
    273
    how about:

    STRIP ( CHAR ( column ) , L , '0' )

    ?

    I'd suggest not to use REPLACE(...), as any '0' would be replaced by blanc ( not only the leading or trailing ones ).



    *ups* I just saw that the original post is several years old ...
    Last edited by umayer; 06-26-07 at 09:06.

  9. #9
    Join Date
    Jun 2007
    Posts
    3
    Actually, even the STRIP function doesn't work on certain OSes. Same reason goes for using RTRIM & LTRIM functions, instead of TRIM function.

    You're right that REPLACE will remove all 0s in the variable, but if you read the code completely, you'll see I'm repopulating the blank fields again with zero.

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    I think ankursmart is correct. First replace banks with 0, then ltrim and rtrim spaces and final replace reminding (non-leading) spaces to 0.

    I had similar problem few months ago. Beside removing leading 0 I had to align text to the right site and the final string had to be exactly 10 characters long.

    Sample (before):
    Code:
    000000000011.11
    000000000222.22
    Sample (after):
    Code:
         11.11
        222.22
    If I remember correctly beside replace, ltrim, rtrim, replace functions I have also used length, case and space functions.

    Hope this helps,
    Grofaty
    Last edited by grofaty; 06-27-07 at 02:42.

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    the solution
    replace(replace(rtrim(ltrim(replace(char(<FIELD_NA ME>), '0', ' '))), ' ', '0'), '.', '')
    fails for negative numbers and numbers with digits after the decimal point.

    Code:
    number          CHAR CAST       REPLACE solution
    10000,050	00010000.050 	1000005
    20000,000	00020000.000 	20000
    -4000,000	-00004000.000	-00004000
    Last edited by Wim; 06-27-07 at 09:02.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  12. #12
    Join Date
    Jun 2007
    Posts
    3
    Wim, the expression will remove all the leading/trailing zeroes & decimals from the field; if we don't want that we can use only ltrim & remove the decimal replace part i.e.

    replace(ltrim(replace(char(<FIELD_NAME), '0', ' ')), ' ', '0')

    It basically depends upon requirements. Leading zeroes may be removed, comma & decimal characters wont be removed.

    Thanks

  13. #13
    Join Date
    Apr 2008
    Posts
    1

    Talking Another option, try VARCHAR() instead of CHAR()

    For example:

    MY_VALUE DECIMAL(9,0)

    SELECT CHAR(MY_VALUE)
    FROM MY_TABLE

    000100045.
    100003567.
    010555365.


    SELECT VARCHAR(MY_VALUE)
    FROM MY_TABLE

    100045.
    100003567.
    10555365.

    So if you want to trim the leading zeros from your string then use VARCHAR().
    If you want to leave the leading zeros (as we do since we are processing SSNs) then use CHAR().

    --
    Dan Glauser
    J2EE Architect

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by fhussain
    SELECT cast(char (OUTSTANDINGBAL) as varchar (30))
    FROM CREDITTable;
    The following does exactly what you want:
    Code:
    SELECT  cast(OUTSTANDINGBAL as varchar (30))
    FROM CREDITTable;
    (At least, on DB2 v8 for z/OS; don't know about other versions.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  15. #15
    Join Date
    Apr 2009
    Posts
    1

    Thumbs down DB2 - Decimal to Varchar without leading zeros

    Here is a sample workaround. You need to know the precision and scale value of FIELD_NAME.

    select CHAR(RTRIM(char(integer(FIELD_NAME))) ||'.'||char(substr(char(decimal(FIELD_NAME,17,2)), 17))) from TABLE_NAME where row_id ='xxxxxx'

    Hope this helps ,
    -Nitya

Posting Permissions

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