CODEWITHSUNDEEP
sqloracle-databaseselectcasting
Abhijith
Abhijith

Reputation: 77

Convert data type in select statement using cast

I have the following query

SELECT SUM(CAST(A.OVERTIME as float)) 
FROM DUTY_TRAN1 A,DUTY_MAS B 
WHERE A.DOC_NO=B.DOC_NO 
  AND A.FROM_DATE='01-JAN-18' 
  AND A.TO_DATE='31-JAN-18'

A.OVERTIME is in varchar. I wish to convert it in float or int in order for SUM function to work. I get error as invalid number.

Sample data of varchar column

enter image description here

Upvotes: 1

Views: 5843

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

The problem is that your OVERTIME has non-numeric characters such as space/alphanumeric in some of the columns. First step is to identify them.

A query using translate will yield all the characters you cannot find in a decimal number.

SELECT * FROM  DUTY_TRAN1 A WHERE translate (OVERTIME,'x.+-0123456789','x') 
IS NOT NULL;

The above query will return the rows that contain anything other than numbers, a "." and "+/-" symbols. This will work if your string has one "." character. For a comprehensive test with decimal in Oracle versions up to 12.1 , you may have to use a REGXP_ or a user defined function. Refer the below link:

https://www.techonthenet.com/oracle/questions/isnumeric.php

Then, you may exclude them from WHERE clause so that the SUM works only with numeric characters.

with t(n)
AS
(
  select '1.5' FROM DUAL UNION ALL
  select '.6' FROM DUAL UNION ALL
  select NULL FROM DUAL UNION ALL
  select '22.5'  FROM DUAL UNION ALL
  select 'a'  FROM DUAL
  )
select  SUM (CAST ( n  AS FLOAT ) ) 
FROM t WHERE translate (n,'x+-.0123456789','x') 
IS  NULL;

If you are running Oracle 12.2, You can specify the DEFAULT return_value ON CONVERSION ERROR clause in CAST.

CAST(OVERTIME 
       AS FLOAT
       DEFAULT 0 ON CONVERSION ERROR)

CAST

Last but not the least, an important advice to you is to use the ANSI standard of JOIN ON syntax rather than the obsolete tablea,tableb syntax for joins

Upvotes: 1

Related Questions