Search code examples
stringdb2currency-formatting

Convert varchar string to Currency format in db2 SQL

I have a column from which i have to extract String and then format it back to US currency format with 2 decimal places.

For example : Column value : {tag}0000020000890|

From this, I have to match the tag and extract 20000890, and format it to 200,008.90

I have extracted the part with below code:

LTRIM(REGEXP_SUBSTR('match pattern', 1,1,'i',,1), '0')

Where match pattern is '\{tag\}(.*?)\|'

With this, I am able to extract 20000890

And then I tried the below to_char and to_number function on top of it to format as comma separated currency with 2 decimal points.

to_char(ltrim(Regexp_substr('match pattern',1,1,'i',1),'0'), '99G999G999D99')

But this throws below error:

Sql error -20447, sqlstate 22007 sqlerrmc 99G999G999D99 Sysibm.Varchar-format

Then I tried,

to_char(to_number(ltrim(Regexp_substr('match pattern',1,1,'i',1),'0')), '99G999G999D99')

But this also throws error:

Sql error -20476, sqlstate 22018 sqlermc DECFLOAT_FORMAT; 99G999G999D99

I'm not sure what causes this error.

Solution

  • The format that you try to use is supported starting from V11.5 only.
    TO_CHAR V11.5
    TO_CHAR V11.1
    Compare the Table 2. Format elements for decimal floating-point to varchar table from both links.

    Moreover, you must cast a string to a numeric value in the 1-st parameter of TO_CHAR:

    SELECT TO_CHAR(DECFLOAT(REGEXP_SUBSTR(V, '\{tag\}(.*?)\|', 1, 1, 'i', 1)), '99,999,999.99') 
FROM (VALUES '{tag}0000020000890|') T(V);