 |
 |
Archives of the TeradataForum
Message Posted: Fri, 26 Aug 2016 @ 10:38:23 GMT
| Subj: | | Re: Eliminate rows where data is having any character other than [0-9] |
| |
| From: | | Dieter Noeth |
Koushik Chandra wrote:
| | I have a column in Teradata where actually DECIMAL(10,0) VALUE expected, but as source side sending some time BAD data so we have to set
the DATATYPE to VARCHAR(100). In the next step we want to join this column with another table's column which is DECIMAL(10,0) DATATYPE. But this
JOIN is failing because of the BAD data. My question is while joining how should we eliminate those rows which are having any character other than
[0-9]. | |
I usually use TO_NUMBER which simply returns NULL when the cast fails. But when you need a DEC(10,0) you might prefer an exact match, up to 10
digits and optional leading or trailing spaces
CASE WHEN REGEXP_SIMILAR(col, ' *[0-9]{1,10} *') = 1
THEN CAST(col AS DEC(10,0))
END
In TD15.10 it's very simple:
TRYCAST(col AS DEC(10,0))
Would be even better if FORMAT was supported, e.g. for DATEs.
Dieter
| |
