how to remove comma in data (decimal datatype)

Database
Enthusiast

how to remove comma in data (decimal datatype)

Hi all,
can anyone please help me on removing the commas in decimal data, i was able to remove using regexp and oreplace(inidviual queries are working fine), but i need to remove the  commas after summing the column

 

first query i am formatting the data to decimal data type with(18,2) and after trying to remove the commas from the o/p

 


SELECT sum(CAST(cOLUMN1 AS DECIMAL(18,2) FORMAT 'Z9,9999')) from TABL_NAME ----11,103,417.83



Query getting error:
---- NOW I WANT TO REMOVE COMMAS IN A AFTER DOING SUM ON A COLUMN

sel oreplace(sum(cOLUMN1),',','') from TABL_NAME

error : Function 'oreplace' called with an invalid number or type of parameters
SELECT_PARALLEL Command Failed.

 

 

tried using regexp -- but no use

SELECT REGEXP_REPLACE(sum(cOLUMN1),',', '', 1, 0) from TABL_NAME --- UNSUPPOSRTED DATA DETECTED

 

 

 

SELECT cOLUMN1, COUNT(*) FROM TABL_NAME GROUP BY cOLUMN1  

O/P

cOLUMN1 COUNT(*)
-----------------------------
-0.25                 1
--------------------------
231,321.21        48

2 REPLIES
Highlighted
Teradata Employee

Re: how to remove comma in data (decimal datatype)

Oreplace and the regexp_ functions all require characters and you are giving them numbers.  Also, if you omit the 3rd argument in the oreplace function it will work.  E.g.,

 

sel oreplace(cast(sum(cOLUMN1) as varchar(38)),',') from TABL_NAME

 

But you could just supply a FORMAT phrase and simplify it even further:

 

sel sum(cOLUMN1) (format'z(31)') from TABL_NAME

Teradata Employee

Re: how to remove comma in data (decimal datatype)

In BTEQ Field Mode, an implicit CAST to VARCHAR applies the FORMAT on output. Just apply the formatting to the result instead of inside the aggregation:

SELECT CAST(SUM(column1) AS DECIMAL(18,2) FORMAT '--(16).2'))

If you were using some other query tool, you would control the formatting on the client side or explicitly CAST the numeric result to VARCHAR after applying the format.