NUMERIC - sql equivalent? 1

[queryjohn]

I have a table column that is defined as CHARACTER. However, this field should only contain numeric values. I would like to run a query that returns all rows where this field contains a non-numeric value (not including nulls).

In COBOL, I'd use the keywords NOT NUMERIC to check for this. Does Teradata provide an SQL function or keyword that is equivalent?

 

[RanD]

By definition, if the attribute is defined as CHAR or VARCHAR, all data in all rows in that column are that datatype.

There is no easy function that I am aware of that would
scan all the characters in a column and return a count (or the actual column value(s) ) for non-numeric values.

You could write a query that would examine each character in each row in the column and see if it is in a list (1, 2, 3, 4 ,5 ,6, 7, 8, 9, 0) and then return that count or value list. columns where a character is not in the list are non-numeric.

To make it a little more 'fun', do you have DECIMAL AND INTEGER types mixed up in the column?

Since the datatype is CHARACTER currently, you can have, for example, any of the following types of strings as CHAR -
'litte house'
'100.10'
'100'

As the table is currently defined, these are all valid CHAR data strings (provided they don't exceed the defined length.

Technically, following your question, the only valid row in the example is '100'. If you have decimals and integers mixed, you need to account for the '.' in your query.

Make it more fun... what about formatting?
'$100.10' ?!?

Once you know the offending rows, you can get them out.
You can always cast integers into decimals, and that way you can resolve the decimal number situation.

cast('$100.10 as decimal(10,2) )
or cast ('100.10' as decimal (10,2) ) will give you 100.10.

Conversely, if you don't care about decimal accuracy, you
can do the following
cast('$100.10 as int )
or cast ('100.10' as int ) will give you 100.

Given only what you've provide in the question, the substring-ing approach I've discussed here is the only thing that leaps to mind, but it is going to be difficult to really answer without knowing what actual universe of data you're trying to 'un-spaghettify'.

Is this a table conversion problem? How many rows are in the table?

You may be better off writing some query that copies non-numeric rows to one table (copy of the original table), and copies numeric rows to a second table copy.

Then again, if you don't care about the non-numeric data at all, you could write a query that deletes a row whenever a character in the string isn't in the list (1, 2, 3, 4, 5 ,6 7, 8, 9, 0) or in the list ('.').

If you can provide more of a description of the problem set, maybe I, or others can provide more guidance.

Hope this helps.

Regards.

RanD

 

[RanD]

I've given it further thought...
I'm assuming from your original question you don't have goofy alphanumerics in your rows. You might try this on the CHAR column...

SELECT * FROM TABLENAME
WHERE COLUMNNAME LIKE ALL ('%0%', '%1%', '%2%', '%3%', '%4%', '%5%', '%6%', '%7%', '%8%', '%9%', '%0%');

This should give you back all rows where at least one character in the character string is numeric.


Of course, if your column contains alphanumeric data, such as 'DG9X$D10DR%', then you're going to have to do some more work, but it is an alternative to writing a massive CASE statement.

RanD