1

Column xy of type 'nvarchar2(40)' in table ABC.

Column consists mainly of numerical Strings

how can I make a

select to_number(trim(xy)) from ABC

query, that ignores non-numerical strings?

Robbert
  • 6,481
  • 5
  • 35
  • 61
Lokomotywa
  • 2,624
  • 8
  • 44
  • 73
  • See this thread - some examples have been given: http://stackoverflow.com/questions/21187819/oracle-sql-query-to-obtain-the-maximum-value-of-a-column-that-has-been-converted/21188117#21188117 – Dmitry Nikiforov Jan 20 '14 at 14:14
  • 1
    Define "numerical". For example, in Oracle `+1.2e-3f` is a valid number, but none of the answers below would correctly recognize it. – Jon Heller Jan 20 '14 at 19:14

3 Answers3

1

In general in relational databases, the order of evaluation is not defined, so it is possible that the select functions are called before the where clause filters the data. I know this is the case in SQL Server. Here is a post that suggests that the same can happen in Oracle.

The case statement, however, does cascade, so it is evaluated in order. For that reason, I prefer:

select (case when NOT regexp_like(xy,'[^[:digit:]]') then to_number(xy)
        end)
from ABC;

This will return NULL for values that are not numbers.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You could use regexp_like to find out if it is a number (with/without plus/minus sign, decimal separator followed by at least one digit, thousand separators in the correct places if any) and use it like this:

SELECT TO_NUMBER( CASE WHEN regexp_like(xy,'.....') THEN xy ELSE NULL END )
FROM ABC;

However, as the built-in function TO_NUMBER is not able to deal with all numbers (it fails at least when a number contains thousand separators), I would suggest to write a PL/SQL function TO_NUMBER_OR_DEFAULT(numberstring, defaultnumber) to do what you want.

EDIT: You may want to read my answer on using regexp_like to determine if a string contains a number here: https://stackoverflow.com/a/21235443/2270762.

Community
  • 1
  • 1
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
-1

You can add WHERE

SELECT TO_NUMBER(TRIM(xy)) FROM ABC WHERE REGEXP_INSTR(email, '[A-Za-z]') = 0

The WHERE is ignoring columns with letters. See the documentation

Vinicius Monteiro
  • 584
  • 8
  • 23