SUBSTRING in ODBC Vs Teradata.Net mode

Database
Enthusiast

SUBSTRING in ODBC Vs Teradata.Net mode

Hello,

I wrote a query that used the SUBSTRING function and worked well in ODBC mode but did not work in Teradata.NET Mode. I referred to earlier posts and was under the assumption that ODBC mode requires ANSI SQL, while Teradata.NET mode accepts both ANSI + Teradata SQL. Which would mean a query that runs in Teradata.Net mode might not run in ODBC mode, but any query that runs in ODBC mode MUST run in Teradata.Net mode. 

Example: Please run the below statements in ODBC mode and Teradata.Net mode

CREATE VOLATILE TABLE SUBSTREX

(

COL1 CHAR(10)

,COL2 CHAR(5)

)

PRIMARY INDEX (COL1)

ON COMMIT PRESERVE ROWS;

SELECT SUBSTRING(COL1,0,10)

FROM SUBSTREX

For me, this worked in ODBC mode, but not in Teradata.NET mode. If I change the word SUBSTRING to SUBSTR, it works in both modes. So, is SUBSTR the ANSI Standard?

Thank you,

Sau

2 REPLIES
Senior Apprentice

Re: SUBSTRING in ODBC Vs Teradata.Net mode

Hi Sau,

SUBSTRING(COL1, 0,10) is ODBC syntax, based on a setting within the ODBC driver options it's automatically rewritten to valid Teradata SQL:

SUBSTRING(COL1 FROM 1 FOR 10) -- ANSI Standard SQL, recommended
SUBSTR(COL1, 1, 10) -- non-Standard, but most DBMSes support it
SUBSTRING(COL1, 1, 10) -- ODBC/Microsoft syntax, don't use
Enthusiast

Re: SUBSTRING in ODBC Vs Teradata.Net mode

Thank you Dieter.