Convert Number to String with Format

Last post 08-05-2013 12:39 PM by bwaldmann. 9 replies.
Page 1 of 1 (10 items)
Sort Posts: Previous Next
  • 07-11-2013 10:26 AM

    Convert Number to String with Format

    In the SQL select statement I need to convert a decimal to a string in a specific format: "0.0000".  This is for the purpose of joining, so it cannot be done afterward.  I used char(6) hoping that would be correct, but it's not returning any results so I must assume it's not.  How can I achieve the desired result?

  • 07-11-2013 10:33 AM In reply to

    Re: Convert Number to String with Format

    Can you post your SQL statement?  How exactly is your decimal column defined?

    I tried the following test, and the results seemed to come out as expected:

    create table td (df decimal(10,4) not null);
    insert into td values (100.1234);
    insert into td values (5.0004);
    insert into td values (33.66);
    select df, cast(df as char(10)) from td;

    <<<<<<<<<<<<<<<<<<<<<<<<
              df               EXPR_1   
    ============   ==========
        100.1234          100.1234 
          5.0004             5.0004   
         33.6600            33.6600  

    3 rows were affected.

    >>>>>>>>>>>>>>>>>>>>>>>>

    Of course, the other option is to cast the string on the other side of the join as a decimal...

  • 07-11-2013 11:14 AM In reply to

    Re: Convert Number to String with Format

    Thanks for your quick response!

    We are accessing a database backend to some software we use, and have no idea whether it's relational or transactional, and we also don't have access to the definitions.  However, looking at the Control Center we can tell the column is a Double (sorry I said Decimal earlier), no precision and scale are listed.

    Unfortunately we can't make the other side a decimal because of what's stored in the column.  The ID field has 2 to 3 IDs concatenated together, none of a specific length or type, so if we do that we get an error because it can't convert text to decimal. 

     select * from "BKARINVL"LEFT JOIN "ISNOTES" ON cast("BKARINVL"."BKAR_INVL_INVNM" as char(5))= SUBSTRING("ISNOTES"."IS_NOTE_ID",9,5) AND cast("BKARINVL"."BKAR_INVL_SCCOG" as char(6)) = SUBSTRING("ISNOTES"."IS_NOTE_ID",15,6)

  • 07-11-2013 12:15 PM In reply to

    Re: Convert Number to String with Format

    What results are you getting if you just select the data with the CAST()?  I tried another test using a double...

    create table td (df double not null);
    insert into td values (1.1234);
    insert into td values (5.0004);
    insert into td values (3.66);
    select df, cast(df as char(6)) from td;

    <<<<<<<<<<<<<<<<<<<<<<<<
       df          EXPR_1
    ======   ======
    1.1234      1.1234
    5.0004      5.0004
    3.66          3.6600

    3 rows were affected.

    >>>>>>>>>>>>>>>>>>>>>>>>

  • 07-12-2013 7:10 AM In reply to

    Re: Convert Number to String with Format

    Very interesting:

     <<<<<<<<<<<<<<<<<<<<<<<<
             BKAR_INVL_SCCOG   EXPR_1
    ========================   ======
                      1.0E-4   1.e-00
                      2.0E-4   2.0000
                      3.0E-4   2.9999
                      4.0E-4   4.0000
                      5.0E-4   5.0000
                      6.0E-4   5.9999
                         0.0   0     
                      7.0E-4   6.9999
                      8.0E-4   8.0000

    16 rows were affected.

    >>>>>>>>>>>>>>>>>>>>>>>>

    (I deleted a bunch of rows that were 0)

  • 07-12-2013 8:48 AM In reply to

    Re: Convert Number to String with Format

    Try casting it to a Decimal (6, 4) before casting it to a string...  You're getting some IEEE float notation & rounding.
  • 07-12-2013 8:56 AM In reply to

    Re: Convert Number to String with Format

    That works perfectly, thanks!

    I wish I didn't have to do all this casting though, seems like it'll add a significant amount of time...  Is there a format setting somewhere to control how doubles are cast to chars?

  • 07-12-2013 9:21 AM In reply to

    Re: Convert Number to String with Format

    If your data types matched, you wouldn't have to do the conversions.  There are no settings for that kind of formatting. However, I've never seen CAST() - even multiple occurrences, produce a significant performance hit.

  • 07-12-2013 9:44 AM In reply to

    Re: Convert Number to String with Format

     Yeah... unfortunately I don't have control over that.

     Thanks so much for your help!!!

  • 08-05-2013 12:39 PM In reply to

    Re: Convert Number to String with Format

    I'm running into another issue - multiples of 10, 100, 1000, and 10000 do not cast to char correctly:

    select
        BKAR_INVL_SCCOG,
        cast(cast(BKARINVL.BKAR_INVL_SCCOG as decimal(5,4)) as CHAR(6))
    from BKARINVL

     <<<<<<<<<<<<<<<<<<<<<<<<
             BKAR_INVL_SCCOG   EXPR_1
    ========================   ======
                      9.0E-4   0.0009
                      0.0010   0.001
                      0.0011   0.0011
                      0.0019   0.0019
                      0.0020   0.002
                      0.0021   0.0021
                      0.0022   0.0022
                      0.0029   0.0029
                      0.0030   0.003
                      0.0031   0.0031
                      0.0032   0.0032
                      0.0039   0.0039
                      0.0040   0.004
                      0.0041   0.0041
                      0.0042   0.0042
                      0.0043   0.0043

     

    Any way around this?

Page 1 of 1 (10 items)