Datacom SQL: the function to convert any data type to String

[THANG TRAN]

Hello,
1)I am using Datacom SQL and cannot yet find a scalar function to convert any data type to String.
In other database, this function is often to_char() like in:
select to_char(salary) from employee
What is the similar function to to_char() in Datacom SQL?
2) A similar question to convert a string to a numeric data type, like integer?

[Karina Pulinx]

Hi, 

Maybe you can use DIGITS

• DIGITS

  Use the DIGITS function to obtain a fixed-length character string representation of a number. Its argument must be an integer or a decimal number. The string of digits that make up the result represent the absolute value of the argument without regard to its scale. Therefore, the result does not include a sign or a decimal point. Leading zeros are included in the result as necessary so that length of string equals:
• 5 if argument is a small integer
• 10 if argument is a large integer
• p

   if argument is a decimal number with precision 

  p.
• The result can be null if the argument can be null; the result is the null value if the argument is null.

  For example, if the data type of COLUMNX is DECIMAL(6,2), and if COLUMNX has a value of -7.27, then DIGITS(COLUMNX) gives '000727' as the result.

Best regards,Karina.

------------------------------
Principal Support Engineer
Broadcom
------------------------------

[Owen Williams]

Hi Thang and Karina,

DIGITS is the simplest option when converting positive-only or unsigned SMALLINT or INTEGER values. But as noted, for signed or decimal numbers you would need to re-insert the sign and decimal place, which can be cumbersome.

For signed/decimal numbers, the CAST...AS CHAR (or VARCHAR) is the simplest solution. Here's an example using the sample tables supplied with Datacom...

SELECT CAST(YTD_WAGES AS VARCHAR(11)) YTD_WAGES,   
       CAST(0 - YTD_WAGES AS VARCHAR(11)) NEG_WAGES
  FROM SYSUSR.PAYROLL;​

Cheers,
Owen

------------------------------
Technical Consultant
Dixons Carphone
------------------------------