Search code examples
db2db2-400db2-luw

How to implement thousand separator for a given text which contains numerical value? Cast and Varchar_format is not working

I need to show 3456789 as 34,56,789 in the output of below select query in db2

SELECT ' RUN TIME: ' CONCAT CURRENT_TIME CONCAT SPACE(39) CONCAT 'PROCESS' CONCAT SPACE(44) CONCAT '3456789' FROM sysibm.sysdummy1 WITH UR;

Current Output is: RUN TIME: 14.13.24 PROCESS 3456689

Solution

  • '3456789' is already a string...

    Assuming your data actually starts out numeric, take a look at varchar_format()

    SELECT ' RUN TIME: ' CONCAT CURRENT_TIME 
  CONCAT SPACE(39) CONCAT 'PROCESS' CONCAT SPACE(44) 
  CONCAT varchar_format(3456789,'9,999,999') 
FROM sysibm.sysdummy1 WITH UR;

    Note it's usually a bad practice to do this formatting in the DB, it's usually best left to the UI.