In a SQL query on Oracle 10g, I need to determine whether a string is numeric or not. How can I do this?
The syntax of the ISNUMERIC function in SQL Server is Check_Expression: Please specify the valid expression or column name on which you want to check for Numeric values. IsNumeric function will check whether the column value is numeric or not. The ISNUMERIC function will return 1 when the user-specified expression is a valid numeric data type.
The function ISNUMERIC returns whether a string is numeric, but will return true for non-integers. So you could use: WHERE ISNUMERIC (str) AND str NOT LIKE '%.%' AND str NOT LIKE '%e%' AND str NOT LIKE '%-%'
SELECT ISNUMERIC ([Check_Expression]) FROM [Source] Check_Expression: Please specify the valid expression or column name on which you want to check for Numeric values. IsNumeric function will check whether the column value is numeric or not. The ISNUMERIC function will return 1 when the user-specified expression is a valid numeric data type.
The syntax of the ISNUMERIC function in SQL Server is. SELECT ISNUMERIC ( [Check_Expression]) FROM [Source] Check_Expression: Please specify the valid expression or column name on which you want to check for Numeric values. IsNumeric function will check whether the column value is numeric or not.
You can use REGEXP_LIKE:
SELECT 1 FROM DUAL
WHERE REGEXP_LIKE('23.9', '^\d+(\.\d+)?$', '')
You ca try this:
SELECT LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' '))) FROM DUAL
where string1 is what you're evaluating. It will return null if numeric. Look here for further clarification
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With