Returning Only Numeric Data
Author: Craig Mullins | | May 26, 2015
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. Of course, there are alternate queries that can also work, such as:
SELECT * FROM TABLENAME WHERE TRANSLATE(VAR, ' ', '0123456789,. ') = '' AND VAR <> '';
Or even:
SELECT * FROM TABLENAME WHERE VAR like '[0-9][0-9][0-9][0-9][0-9]';
Choose one that works the best for you and away you go!
This post was originally posted on Craig Mullins’ blog at http://db2portal.blogspot.com.
Related Posts
How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified
The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.
Data Types: The Importance of Choosing the Correct Data Type
Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.
How to Recover a Table from an Oracle 12c RMAN Backup
Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.