INSTR and SUBSTRING are not working in SHELL SCRIPT

Database
Enthusiast

INSTR and SUBSTRING are not working in SHELL SCRIPT

Hi 

Iam Using INSTR and SUBSTRING in UNIX shell script. They are working in Teradata sql assistant but they both are not working in UNIX SHELL SCRIPT.

I changed SUBSTRING to SUBSTR and it worked. But i still have problem with INSTR. Can any one help me out.

Example :

select

case when  SubRegion like '%REGION%' then SubRegion  else SubRegion || ' '

 || 'REGION' end REGION_NAME,

SUBSTR(nodes  FROM instr(nodes,'-',1,1)+1 for instr(nodes,'-',1,1)-1) AS node, 

SgSpeed, 

SgUtil,

PortCount, 

CAST(WeekEndingDate as DATE) WEEKENDINGDATE

FROM RNL_VIEWS.WT_CmtsSgUtil

WHERE instr(nodes,'-') > 0

and WeekEndingDate =  '2014-12-06'

ERROR:

SUBSTR(nodes  FROM instr(nodes,'-',1,1)+1 for instr(nodes,'-',1,1)-1) AS n

ode, 

                   $

 *** Failure 3706 Syntax error: expected something between the word 'nodes' 

 and the 'FROM' keyword.

                Statement# 1, Info =582 

 *** Total elapsed time was 1 second.

Thanks

Naveen

Tags (1)
3 REPLIES
Junior Contributor

Re: INSTR and SUBSTRING are not working in SHELL SCRIPT

Hi Naveen,

There are two variations of substring in Teradata:

SUBSTRING(str FROM startpos FOR length) -- Standard SQL
SUBSTR(str, startpos, length) -- Teradata SQL


SUBSTR(str FROM startpos FOR length) doesn't exist, but the ODBC driver might rewrite ODBC-style SUBSTRING(str, startpos, length). This is also done for other ODBC-functions like LEFT or MONTH. Whenever you try to submit such a query using CLI/.NET/JDBC it will fail.

To disable this rewrite you to need to check the 'Disable Parsing' in the ODBC driver's options.

Regarding INSTR, this is included since TD14 and should not cause any error.

Enthusiast

Re: INSTR and SUBSTRING are not working in SHELL SCRIPT

For SUBSTRING i used SUBSTR and unix shell script recognized. But i Have Problem with INSTR in Shell script

Junior Contributor

Re: INSTR and SUBSTRING are not working in SHELL SCRIPT

The variation you wrote will never work in shell (=BTEQ), remove the INSTR and you still get the same error while INSTR standalone will work.