h1

[Oracle] – Check for any NON-NUMERIC value in a column

March 30, 2007

select *
from table_name
where
instr(translate(column_name,
‘0123456789’,
‘XXXXXXXXXX’),’X’) != length(column_name);

Only record with NON-NUMERIC value in column_name will be displayed.

Advertisements

5 comments

  1. This is an extremely useful script for pulling out exceptions, but I’m not familiar enough with Oracle to write an equivalent script for MS SQL Server. Do you know of an equivalent in MS SQL (2005) for the ‘translate’ function?


  2. i do not have exact match, but you can refer to the following link:
    http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/32f65303d22e343

    This article is about how to get rid of invalid char from phone no, using replace and case when syntax. You can modified it to substring of your string, and for each char, you check if the char is equal to 0 to 9, replace it with 0, else replace it with ” [empty string]. After that, simply count the no of zero (0) and if it matches the length of the string, that means that the string is numeric, if not, some char is not number and therefore the whole string is not numeric.

    Hope it helps.


  3. Simple IF test for non-numeric:
    IF LENGTH(REPLACE(TRANSLATE(column_name, ‘0123456789’, ‘@@@@@@@@@@’), ‘@’, NULL)) > 0 THEN column value includes a non-numeric character;


  4. Simple IF test for non-numeric:
    IF LENGTH(REPLACE(TRANSLATE(column_name, ‘0123456789’, ‘@@@@@@@@@@’), ‘@’, NULL)) > 0 THEN column value includes a non-numeric character;


  5. The best way to do it is given below:

    SELECT DECODE(LENGTH(TRANSLATE(‘&value’,’_0123456789′,’_’)),NULL,1,0) “IS_NUMERIC” FROM dual

    It will return 1 if the value is numeric else it will be 0.

    Thanks
    Subha



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: