SQL Question about use of LEFT, RIGHT and SUBSTR functions

Database

SQL Question about use of LEFT, RIGHT and SUBSTR functions

WHY DOES QRY1 WORK AND QRY2 WORKS BUT QRY3 DOES NOT?

QRY1:

SELECT   SUBSTR(P.PHONE,1,3) ,

              SUBSTR(P.PHONE,4,3) ,

              SUBSTR(P.PHONE,7,4)

FROM    PhoneNumbers  p ;

QRY2:

SELECT  LEFT(P.PHONE,3),

               RIGHT(P.PHONE,4)

FROM     PhoneNumbers  p ;

QRY3: - error message= 3706 - ”Expected something between the Select keyword and the Left keyword”

SELECT   LEFT(P.PHONE,3) ,

               SUBSTR(P.PHONE,4,3) ,

               RIGHT(P.PHONE,4)

FROM     PhoneNumbers  p ;

Thanks for any insight you can give - things like this puzzle me! 

3 REPLIES
Senior Apprentice

Re: SQL Question about use of LEFT, RIGHT and SUBSTR functions

Hi Richard,

if you run TD15.10 all three should work, before both LEFT and RIGHT were not built-in, but the ODBC (JDBC?)  driver might rewrite them to vaild TD syntax.

So what's your TD release and how do you connect, ODBC/JDBC/.NET?

Re: SQL Question about use of LEFT, RIGHT and SUBSTR functions

TD14.1 and I am currently using an ODBC connection

Teradata Employee

Re: SQL Question about use of LEFT, RIGHT and SUBSTR functions

>>> (JDBC?)  driver might rewrite them to vaild TD syntax

Since TTU 8.1 (over 10 years ago), the Teradata JDBC Driver only replaces JDBC escape syntax inside curly braces { } with the proper Teradata SQL syntax. The Teradata JDBC Driver does not change any SQL request text outside JDBC escape syntax curly braces.

Here is a link to the Teradata JDBC Driver User Guide section about JDBC escape syntax:

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#BGBDDAFD