Forum Stats

  • 3,782,433 Users
  • 2,254,645 Discussions
  • 7,880,078 Comments

Discussions

Categories

Convert Char To Number

Angus123
Angus123 Member Posts: 100 Red Ribbon
in SQL & PL/SQL

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

· Share on TwitterShare on Facebook

Best Answer

  • Frank Kulash
    Frank Kulash 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?

    · Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash 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.

    · Share on TwitterShare on Facebook
  • Angus123
    Angus123 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

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash 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?

    · Share on TwitterShare on Facebook