“Safe” TO_NUMBER()

by

From Oracle Database 12c Release 2 you could use TO_NUMBER with DEFAULT ... ON CONVERSION ERROR:

SELECT TO_NUMBER('*' DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;

Or CAST:

SELECT CAST('*' AS NUMBER DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;

db<>fiddle demo

More Related Contents:

  1. ANYONE CAN TELL ME THAT WHY THIS CODE IS GIVING ME AN ERROR OF TABLE OT CLUSTER KEY WORD IS MISSING? [closed]
  2. How do I escape a reserved word in Oracle?
  3. How to put more than 1000 values into an Oracle IN clause [duplicate]
  4. ORA-30926: unable to get a stable set of rows in the source tables
  5. ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
  6. Fetch Oracle table type from stored procedure using JDBC
  7. ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client
  8. Using the “IN” clause with a comma delimited string from the output of a replace() function in Oracle SQL
  9. NLS_NUMERIC_CHARACTERS setting for decimal
  10. Oracle JDBC and Oracle CHAR data type
  11. What is the minimal setup required to deploy a .NET application with Oracle client 11?
  12. Does Oracle have an equivalent of SQL Server’s table variables?
  13. check if “it’s a number” function in Oracle
  14. Reason why oracle is case sensitive?
  15. ORA-12170: TNS:Connect timeout occurred
  16. Faster alternative in Oracle to SELECT COUNT(*) FROM sometable
  17. How to create and use temporary table in oracle stored procedure?
  18. Oracle date difference to get number of years
  19. SQL*Plus how to accept text variable from prompt?
  20. How to find difference b/w TIMESTAMP format values in Oracle?
  21. SQL Fiddle Output Error
  22. oracle PL/SQL how to calculate range ip for IPv6 cidr
  23. Trying to export a Oracle via PL/SQL gives a date of 0000-00-00
  24. Oracle: how to add minutes to a timestamp?
  25. How to drop multiple interval partitions based on date?
  26. How to list active / open connections in Oracle?
  27. ORA-01830: date format picture ends before converting entire input string / Select sum where date query
  28. How to generate a version 4 (random) UUID on Oracle?
  29. Find out the history of SQL queries
  30. How to test an Oracle Stored Procedure with RefCursor return type?

Leave a Comment