Thursday 10 February 2011

isNumber or isNumeric method for Oracle

Oracle does not have these functions, however you can use oracle regular expressions to achive this:

1. Example 1:
SELECT
*
FROM
_TABLE_NAME_
WHERE
REGEXP_LIKE(_COLUMN_NAME_,'^-?[[:digit:],.]*$')

This will return the _COLUMN_NAME_ values that are numeric.

2. Example 2:
select _COLUMN_NAME_,
case when regexp_like(_COLUMN_NAME_, '^-?[[:digit:],.]*$')
then
'Numeric'
else 'Non-Numeric' end as type
from _TABLE_NAME_

3. You can also use it as:
SELECT
*
FROM
_TABLE_NAME_
WHERE
NOT REGEXP_LIKE(_COLUMN_NAME_,'^-?[[:digit:],.]*$')
This will return the _COLUMN_NAME_ values that are non-numeric.

No comments:

Post a Comment

Azure OpenAI Architecture Patterns & Deployment Patterns

Sharing some useful links that will help customers architect Azure OpenAI solution using the best practices: (1) Azure OpenAI Landing Zone r...