Convert Char To Number

Angus123

Hi

I have a column which is a CHAR(40).

It contains data like 20210930 or data like 2021-08-11 00:00:00.000 +02:00.

I want to convert to a number like 20210930 or 20210811

This works

SELECT TO_NUMBER(TO_CHAR(TO_DATE('20210930','YYYYMMDD'), 'YYYYMMDD')) FROM dual;

How do I convert it to a number like 20210811

I have tried

SELECT TO_NUMBER(TO_CHAR(TO_DATE('2021-08-11 00:00:00.000 +02:00','YYYYMMDD'), 'YYYYMMDD')) FROM dual;

and several variations but always seem to get an error

Ora-01843 Not a valid month

Regards

Gus

Frank Kulash

Hi,

If you want to convert the first eight digits ('0' thorough '9') to an integer, ignoring all characters other than digits, then you can do it this way:

TO_NUMBER ( SUBSTR ( TRANSLATE ( str
	  	   	       , '0123456789' || str
		   	       , '0123456789'
		   	       )
		    , 1
  		    , 8
		    )
	  )

What version of Oracle are you on? In very old versions, the TRANSLATE part to remove non-digits is a little more complicated.

If these strings are supposed to represent dates, why not convert them to DATEs rather than NUMBERs?

Frank Kulash

Hi, @Angus123

So, you want '2021-08-11 00:00:00.000 +02:00' converted to 21210811. Why not 202108110000000000200? Post you exact requirements, along with a little sample data (CREATE TABLE and INSERT statements) and the exact results you want from that sample data.

Angus123

Hi Frank

create table TEST( test_1 char(100));

INSERT INTO TEST('20210930');

INSERT INTO TEST('2021-08-11 00:00:00.000 +02:00');

Expected results as numbers

20210930

20210811

Regards

Gus

Frank Kulash

Hi,

If you want to convert the first eight digits ('0' thorough '9') to an integer, ignoring all characters other than digits, then you can do it this way:

TO_NUMBER ( SUBSTR ( TRANSLATE ( str
	  	   	       , '0123456789' || str
		   	       , '0123456789'
		   	       )
		    , 1
  		    , 8
		    )
	  )

What version of Oracle are you on? In very old versions, the TRANSLATE part to remove non-digits is a little more complicated.

If these strings are supposed to represent dates, why not convert them to DATEs rather than NUMBERs?