Using the 'Right' Keyword in a query?

Database

Using the 'Right' Keyword in a query?

Can anyone give me some insight into what im doing wrong here :

I am simply trying to use the Right function to get the most righthand 4 digits/characters from a query.

Eg. Right(Case_Size,4)

When I run it gives an 'expected a something and a something between ( and something' error which is not actually pointing to that line. As soon as I substitute the 'Right' keyword for 'Left' just to test the query it works perfectly - so I know that my query is correctly structured. The wierd thing is that it doesnt even work on a small standalone query like Select Right('TestString',5) from...

Since the Left command works im just assuming that the Right command should be the same syntax. Ive searched around and it seems to be viable.

Am I doing something wrong liko ommiting a comma etc?

Thanks

7 REPLIES
Teradata Employee

Re: Using the 'Right' Keyword in a query?

RIGHT and LEFT are keywords for joing tables not for substrings. You may have a UDF installed on your system that also defines string functions. As far as I know there is not a Teradata defined function that will pull the right-most characters of a string.

This will do what you need:

select substr(case_size, character_length(case_size) -4)

Re: Using the 'Right' Keyword in a query?

Great , nearly had that substring approach worked out but was getting frustrated as apparently im just not smart enough to use char_length - case_size effectively!

Thanks for the help jeff_o.
Senior Apprentice

Re: Using the 'Right' Keyword in a query?

Hi Daniel,
LEFT and RIGHT are not Standard SQL, but ODBC SQL.

Left only works in QueryMan/SQL Assistant, when you switch on
"Allow Use of ODBC SQL Extensions in Queries" in Options -> Query

It is recommended to uncheck that option (although it's checked by default), because your query will fail e.g. using BTEQ.

Check the Teradata ODBC manual for other functions like MONTH and LTRIM

Dieter

Re: Using the 'Right' Keyword in a query?

Thanks for the info Dieter - Ive never really known what that ' Allow use of ODBC in SQL ' option allowed and will disable it.
Enthusiast

Re: Using the 'Right' Keyword in a query?

How to select the left substr?
Enthusiast

Re: Using the 'Right' Keyword in a query?

Use "substr":

Select substr(columnname,1,xx)...

where 'xx' is the length you want. If you want the right substring, it's a little more difficult:

select substr(columnname,length(columnname) - xx + 1, xx)

where 'xx' is the length you want.

Hope that helps.
Enthusiast

Re: Using the 'Right' Keyword in a query?

Hi Barry,

That worked. Thanks for the inputs:-)