> -----Original Message-----
> From: Robert Everland III [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 09, 2006 2:52 PM
> To: CF-Talk
> Subject: SQL Challenge
>
>
> Is there a way in MSSQL and Oracle or either that will allow you to do a
> select on a varchar field and be able to see if that record can be
> converted to a number? We have a ton of databases that used varchar to
> handle numbers and are getting issues now that some records have gotten
> data other than a number in them. I want to convert them to an integer
> field but can't do that until we fix the problem records.
>
Sadly, Oracle doesn't provide a native function like isNumeric() to do
this, to my knowledge. However, you don't specify which version of
Oracle, so I'll give you two solution, neither of which I can claim
credit for, but have lived in my code snippet archive for a while:
Pre-Oracle 10g:
Create a function called isNumber:
CREATE OR REPLACE FUNCTION isNumber(p_val IN VARCHAR2) RETURN NUMBER IS
l_val NUMBER;
BEGIN
l_val := TO_NUMBER(p_val);
RETURN 1;
EXCEPTION WHEN VALUE_ERROR THEN
RETURN 0;
END;
Then, in your query, you'd just do something like:
SELECT
isNumber('2006') AS isNumeric
FROM
dual;
That should return 1.
SELECT
isNumber('Dave') AS isNumeric
FROM
dual;
should return 0.
If you're using Oracle 10g, they've added great RegEx support (it was
there before, but with some packages I honestly never used, so I don't
know the syntax):
SELECT
id
FROM
myTable
WHERE
REGEXP_LIKE (myColumn, '^[0-9]+$');
That should return the *non*-numeric values from that column.
These are untested, but should work.
Regards,
Dave.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231836
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
