Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I determine if a string is numeric in SQL?

In a SQL query on Oracle 10g, I need to determine whether a string is numeric or not. How can I do this?

like image 497
AndrewL Avatar asked Apr 14 '11 17:04

AndrewL


People also ask

How to check for numeric values in SQL Server?

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.

How do I check if a string is numeric or not?

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 '%-%'

How to check if a column value is numeric in MySQL?

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.

What is the syntax of isnumeric function in SQL Server?

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.


2 Answers

You can use REGEXP_LIKE:

SELECT 1 FROM DUAL
WHERE REGEXP_LIKE('23.9', '^\d+(\.\d+)?$', '') 
like image 68
Chandu Avatar answered Sep 16 '22 17:09

Chandu


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

like image 21
Adriano Carneiro Avatar answered Sep 16 '22 17:09

Adriano Carneiro