Forum Stats

• 3,782,433 Users
• 2,254,645 Discussions

Discussions

Convert Char To Number

Member Posts: 100 Red Ribbon

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

• Member, Moderator Posts: 41,396 Red Diamond
edited Oct 26, 2021 1:34PM Accepted Answer

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?

• Member, Moderator Posts: 41,396 Red Diamond

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.

• Member Posts: 100 Red Ribbon

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

• Member, Moderator Posts: 41,396 Red Diamond
edited Oct 26, 2021 1:34PM Accepted Answer

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?