Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    China
    Posts
    9

    Unanswered: Looking for such a function

    To the number 1234567.89 ,I want to get a string like 1,234,567.89

    (means the amount of money).

    Though I can use Oracle's function to_char(v,'999,999,990.99') resolve

    the problem temporarily,I want to know whether DB2 has a function

    that can do the same thing.

    thanks!!

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    You could write your own.

    Here's something to start you off...

    CREATE FUNCTION CURRENCY (ARG VARCHAR(1000))
    RETURNS VARCHAR(1000)
    LANGUAGE SQL
    DETERMINISTIC
    RETURN
    WITH CHAR_POSITIONS(STRING, INDEX, COUNTER) AS
    (
    VALUES ( CHAR('00000'||ARG), LENGTH('00000'||ARG) - 6, INT(0) )
    UNION ALL
    SELECT SUBSTR(STRING,1,INDEX)||','||SUBSTR(STRING,INDEX+1 ), INDEX - 3, COUNTER + 1
    FROM CHAR_POSITIONS
    WHERE INDEX > 5
    )
    SELECT SUBSTR(STRING,6)
    FROM CHAR_POSITIONS
    WHERE COUNTER IN (SELECT MAX(COUNTER) FROM CHAR_POSITIONS);


    You could probably tidy this up a bit. It must take a char input. I've only accounted for numbers of the format 99999.99.

    HTH

  3. #3
    Join Date
    Jul 2003
    Location
    China
    Posts
    9

    Thank you,^_^

    Originally posted by Damian Ibbotson
    You could write your own.

    Here's something to start you off...

    CREATE FUNCTION CURRENCY (ARG VARCHAR(1000))
    RETURNS VARCHAR(1000)
    LANGUAGE SQL
    DETERMINISTIC
    RETURN
    WITH CHAR_POSITIONS(STRING, INDEX, COUNTER) AS
    (
    VALUES ( CHAR('00000'||ARG), LENGTH('00000'||ARG) - 6, INT(0) )
    UNION ALL
    SELECT SUBSTR(STRING,1,INDEX)||','||SUBSTR(STRING,INDEX+1 ), INDEX - 3, COUNTER + 1
    FROM CHAR_POSITIONS
    WHERE INDEX > 5
    )
    SELECT SUBSTR(STRING,6)
    FROM CHAR_POSITIONS
    WHERE COUNTER IN (SELECT MAX(COUNTER) FROM CHAR_POSITIONS);


    You could probably tidy this up a bit. It must take a char input. I've only accounted for numbers of the format 99999.99.

    HTH

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    I wasn't happy with that, so I did this...

    It's a bit more flexible because it accepts a decimal value but it is limited to the max INT value (2^31). It won't work with negative values, so there's still some work to do there.

    I just wish I could figure an easy way to translate decimals to chars without the leading 0's!

    CREATE FUNCTION CURRENCY2 (ARG decimal(12,2))
    RETURNS VARCHAR(20)
    LANGUAGE SQL
    DETERMINISTIC
    RETURN
    WITH CHAR_POSITIONS(STRING, INDEX, COUNTER) AS
    (
    VALUES (
    RTRIM(CHAR(INT(SUBSTR(CHAR(ARG),1,LOCATE('.',CHAR( ARG)) - 1))))
    || SUBSTR(CHAR(ARG),LOCATE('.',CHAR(ARG)))
    ,
    LENGTH(RTRIM(CHAR(INT(SUBSTR(CHAR(ARG),1,LOCATE('. ',CHAR(ARG)) - 1))))
    || RTRIM(SUBSTR(CHAR(ARG),LOCATE('.',CHAR(ARG))))) - 6
    , 0 )
    UNION ALL
    SELECT SUBSTR(STRING,1,INDEX)||','||SUBSTR(STRING,INDEX+1 ), INDEX - 3, COUNTER + 1
    FROM CHAR_POSITIONS
    WHERE INDEX > 0
    )
    SELECT RTRIM(STRING)
    FROM CHAR_POSITIONS
    WHERE COUNTER IN (SELECT MAX(COUNTER) FROM CHAR_POSITIONS)
    ;

  5. #5
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    This should fix the 'no negatives' bug...

    CREATE FUNCTION CURRENCY2 (ARG decimal(12,2))
    RETURNS VARCHAR(20)
    LANGUAGE SQL
    DETERMINISTIC
    RETURN
    WITH CHAR_POSITIONS(STRING, INDEX, COUNTER, SIGN) AS
    (
    VALUES
    (
    RTRIM(CHAR(ABS(INT(ARG)))) ||
    SUBSTR(CHAR(ARG),LOCATE('.',CHAR(ARG)))
    , LENGTH(RTRIM(CHAR(ABS(INT(ARG)))) ||
    RTRIM(SUBSTR(CHAR(ARG),LOCATE('.',CHAR(ARG))))) - 6
    , 0
    , LOCATE('-',CHAR(ARG))
    )
    UNION ALL
    SELECT SUBSTR(STRING,1,INDEX)||','||SUBSTR(STRING,INDEX+1 )
    , INDEX - 3
    , COUNTER + 1
    , SIGN
    FROM CHAR_POSITIONS
    WHERE INDEX > 0
    )
    SELECT CASE WHEN SIGN = 0 THEN RTRIM(STRING)
    ELSE '-'||RTRIM(STRING) END
    FROM CHAR_POSITIONS
    WHERE COUNTER IN (SELECT MAX(COUNTER) FROM CHAR_POSITIONS)
    ;
    Last edited by Damian Ibbotson; 08-01-03 at 10:38.

Posting Permissions

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