1

I have a line of code in Oracle and I had to convert it into Teradata. The Oracle query is

 /* add to avoid invalid number due to junk in column */

AND regexp_instr(table.column, ''[^[:digit:]]'', 1, 1)  = 0

The code I have written in Teradata 

AND (CASE WHEN (POSITION('' '' IN TRIM(table.column)) > 0) OR (UPPER(TRIM(table.column))              
        (CASESPECIFIC) <> LOWER(TRIM(table.column)) (CASESPECIFIC)) 
          THEN 1 ELSE 0 end ) = 0

The column is defined as a VARCHAR(20) but I only want to select rows where the data is all numeric. I cannot verify the Teradata query as it is a very long-running query and I don't have access to create tables or rather I can not verify the out put on the database I have. I some how tried and it looks like it works but I once wanted to verify the syntax and my understanding of REGEXP_INSTR.

user708477
  • 147
  • 2
  • 4
  • 14

1 Answers1

0

If I am reading correctly and based on my testing this will break your logic (both return 1):

SELECT (CASE WHEN (POSITION('' '' IN TRIM('1234')) > 0) OR (UPPER(TRIM('1234'))              
        (CASESPECIFIC) <> LOWER(TRIM('1234')) (CASESPECIFIC)) 
          THEN 1 ELSE 0 END )

SELECT (CASE WHEN (POSITION('' '' IN TRIM('abcd ef1')) > 0) OR (UPPER(TRIM('abcd ef1'))              
        (CASESPECIFIC) <> LOWER(TRIM('abcd ef1')) (CASESPECIFIC)) 
          THEN 1 ELSE 0 END )

The Teradata Developer's Exchange contains a library of Oracle functions that have been converted to Teradata UDF's that may help you address this problem. With a little effort you could write your own UDF around the isdigit() C function. (isdigit)

It may not be helpful now, but one of the recently announced Teradata 14.0 features is support of regular expressions.

EDIT: Added TD 14 example with REGEXP_INSTR that should solve the problem

SELECT table.column
  FROM table
WHERE REGEXP_INSTR(table.column, '[^[digit]])') = 0;
Rob Paller
  • 7,736
  • 29
  • 26