LENGTH not working

Database
Enthusiast

LENGTH not working

Hi.

I have some problems with Length calculating function in TERADATA. I am using Teradata SQL Assistant 13.11.0.05, some of my colleges are using version 7.????.

I cannot use the LENGTH function but the CHARACTER_LENGTH and the CHARACTER functions are working ok. I cannot us the LENGTH function in version 13. I get the following error:

SELECT LENGTH('   ABC   ');

SELECT FAILED. [3706] Syntax error expected something between ‘(‘ and a string or a Unicode literal.

My college has both Assistant version 13 and 7 installed. He had problems with the version 7 where the LENGTH function was not working. I have only installed Assistant 13 and I cannot make the LENGTH work. The 2 other are working fine:

SELECT CHARACTERS(TRIM(TRAILING FROM '  ABC   '));

SELECT CHARACTER_LENGTH(TRIM(TRAILING FROM '   ABC   '));

Peter Schwennesen

2 REPLIES
Senior Apprentice

Re: LENGTH not working

Hi Peter,

LENGHT is an ODBC function like LTRIM, RTRIM, MONTH which is replaced by the ODBC parser with some valid Teradata SQL, IMHO it should not be used at all due to the problems you encouter.

 

It's working in SQLA when you switch off "Diasble Parsing" in the OBDC driver options, in older releases this behaviour could be controlled by an SQLA option on the query tab "Allow use of ODBC SQL extensions in queries".

 

But, when you switch it on your query will fail when you run it using any other connection (JDBC, .NET, CLI) or when you try to use it in a CREATE TABLE AS or CREATE VIEW (the parser only replaces it for DML).

In TD14 there's a built-in LENGTH function, in TD13.10 i would suggest to create it as a SQL UDF.

Dieter

Enthusiast

Re: LENGTH not working

Hi Dieter

Thanks for the information. Glad I alwas has been using the CHARACTER_LENGTH function.

I will update my coworkers.

br

Peter Schwennesen