ORA-01722: invalid number with RPAD-CodePudding

There is a case where I want to take the date of birth and pad it with eight ' ' empty spaces

This is what I have CASE WHEN Date_of_Birth IS NULL THEN rpad(' ', 8, ' ') ELSE substr( (replace( to_char(to_date(DATE_OF_BIRTH,'YYYY-MM-DD HH24:MI:SS'), 'MM/DD/YYYY'), '/', '' ) rpad(' ',(length(' ') * 8),' ')) ,1,8 ) END Date_of_Birth_1,

The DATE_OF_BIRTH is NVARCHAR2. I'm getting the error saying that RPAD is an invalid number,

I figured changing the date to a to_char would change it to a string then to add the spaces at the end of it.

CodePudding user response：

by the sign, did you mean to concatenate strings like in Javascript? String concatenation in Oracle uses ||

Aside from that, you should also check to make sure date_of_birth is in fact in the YYYY-MM-DD HH24:MI:SS format in your data or you can get the same error.

CodePudding user response：

It is the concatenation operator (as Paul already said).

Though, you could simplify that code.

date_of_birth is the original value, stored in the table
date_of_birth_1 is result based on your code
date_of_birth_2 is result on my (simplified) code

SQL> set null 'null'
SQL> select
  2  id,
  3  date_of_birth,
  4  --
  5  CASE WHEN Date_of_Birth IS NULL THEN
  6          rpad(' ', 8, ' ')
  7       ELSE substr( (replace( to_char(to_date(DATE_OF_BIRTH,'YYYY-MM-DD HH24:MI:SS'), 'MM/DD/YYYY'), '/', '' ) || rpad(' ',(length(' ') * 8),' ')) ,1,8 )
  8  END Date_of_Birth_1,
  9  --
 10  case when date_of_birth is null then '        '
 11       else to_char(to_date(date_of_birth, 'yyyy-mm-dd hh24:mi:ss'), 'mmddyyyy')
 12  end date_of_birth_2
 13  from test;

        ID DATE_OF_BIRTH        DATE_OF_BIRTH_1      DATE_OF_BIRTH_2
---------- -------------------- -------------------- --------------------
         1 2020-08-25 13:20:28  08252020             08252020
         2 2015-03-18           03182015             03182015
         3 null

SQL>

CodePudding user response：

You appear to want to convert the string DATE_OF_BIRTH column from YYYY-MM-DD HH24:MI:SS format to MMDDYYYY format and if it is NULL then provide 8 spaces.

That can be achieved without the complicated use of string functions using:

COALESCE(
  TO_CHAR(TO_DATE(DATE_OF_BIRTH,'YYYY-MM-DD HH24:MI:SS'), 'MMDDYYYY'),
  '        '
) AS Date_of_Birth_1,

Note: there is no need to replace the / separators in a date or to remove trailing spaces if you never include them in the first palce.