Thursday, March 09, 2006

Returning Only Numeric Data

I frequently get e-mail from folks asking about ways to accomplish things in DB2 and SQL. A recent question I got went something like this:

Is there any option to check whether data “IS NUMERIC” in a DB2 table? We want to examine CHAR data but return only those where the entire data consists only of numbers. For example, can we write a query like this?

SELECT *
FROM TABLENAME
WHERE VAR IS NUMERIC.

The VAR variable is defined as a CHAR(5) column and it will contain data like below.
123aa
2234a
34256
32102
add91

Out of the above 5 records we would want only the 3rd and 4th records to be returned. We tried CAST (VAR as integer), but any other option is there for fetching like above. Please explain

Well, if you try to cast non-numeric data to numeric you will get an error. But you can test the data beforehand - digit by digit - using the SUBSTR function. You’d have to break the VAR column down using SUBSTR to get each individual character and test whether that character is between 0 and 9 – then only if all characters are between 0 and 9 would the result be returned.

Here is what the SQL might look like:

SELECT *
FROM TABLENAME
WHERE SUBSTRING(VAR,1,1) BETWEEN '0' AND '9'
AND SUBSTRING(VAR,2,1) BETWEEN '0' AND '9'
AND SUBSTRING(VAR,3,1) BETWEEN '0' AND '9'
AND SUBSTRING(VAR,4,1) BETWEEN '0' AND '9'
AND SUBSTRING(VAR,5,1) BETWEEN '0' AND '9';

This will return only those rows where every digit in the VAR column is a number between zero and nine.
at

13 comments:

  1. Anonymous6:48 PM

    You could use the following SQL:
    SELECT *
    FROM TABLENAME
    WHERE UCASE(VAR) = LCASE(VAR);

    /tage gejl

    ReplyDelete
  2. Craig S. Mullins8:46 PM

    Good idea. That SQL should work if the only characters are numbers or the letters A-Z. If there are other options (such as punctuation or special characters) then it would not work as UCASE and LCASE only translate A-Z (or a-z).

    ReplyDelete
  3. Anonymous11:52 PM

    This example shows a good example where a stored procedure comes in handy. Like with Oracle, you'd write a Function which takes a value and returns a BOOL telling you whether a column is numeric or not.

    So then your SQL would look like:

    SELECT
    *
    FROM
    TABLENAME
    WHERE
    IS_NUMERIC(VAR) = TRUE;

    Writing this in normal SQL would be slow and painful. There really is no clean and effective way to handle this situtation.

    - Richard Burton

    ReplyDelete
  4. Anonymous12:54 AM

    -- You could also use the following (var4 is 4 bytes):

    SELECT *
    FROM TABLENAME
    WHERE TRANSLATE(VAR4,'##########','1234567890') = '####'
    ;

    -- Scott Shatsky

    ReplyDelete
  5. Anonymous4:03 PM

    I don't know if the following helps :
    To check for the any numeric data (does not currently support commas)
    SELECT C1
    ,case
    when rtrim(ltrim(c1)) in ('-','+','-.','+.','','.')
    then 'Non-Numeric'
    when replace(translate(rtrim(ltrim(c1)),'x ',' 0123456789'),' ','') in ('-','+','-.','+.','','.')
    then 'Numeric'
    else 'Non-Numeric'
    end
    FROM TEMP1
    ;

    ReplyDelete
  6. Anonymous8:10 AM

    What in case you have XXXXX in ZIpcode instead of Numerics.
    What Query you use in design view?

    ReplyDelete
  7. Unknown7:40 AM

    SELECT * FROM table
    WHERE TRANSLATE(TRANSLATE(field, '0123456789, ', '0000000000 '), ' ', '0123456789') <> ' '
    AND field <> ''

    ReplyDelete
  8. Unknown7:45 AM

    the above works for DB2

    ReplyDelete
  9. Phoenix10:15 AM

    It is Shame that DB2 does not have IS NUMERIC check

    ReplyDelete
  10. natan12:51 AM

    here is a nice way :
    select * from
    tablename
    where
    HEX(VAR) LIKE '%F_F_F_F_F%'

    ReplyDelete
  11. Anonymous10:50 AM

    UCASE and LCASE worked also LIKE '%F_F_F_F_F%'. I tested the other ways and they don't work, I am using it in a credit card field which should contain ONLY encrypted data

    ReplyDelete
  12. Anonymous6:19 AM

    This is optimal, easily and quickly

    SELECT *
    FROM Table
    WHERE TRANSLATE(Field,
    ' ',
    '0123456789,. '
    ) = ''
    AND Field <> ''

    ReplyDelete
  13. Karthik R11:41 PM

    Select * from table where VAR like '[0-9][0-9][0-9][0-9][0-9]'

    ReplyDelete