Escape Characters, Operators & Date Conversions

Database
Enthusiast

Escape Characters, Operators & Date Conversions

Hi
I am trying to find out answers to the following questions, which will help me in porting my application to Teradata. Some of the answers I have been able to trace, but probably would need some verifying from experts. I need help in finding out answers to the ones whom I have not been able to nail.

1. Mapping of Text data type for Teradata
Long Varchar

2. Is varchar in Teradata Unicode compliant? If not, is there any Unicode compliant data type in Teradata?
Yes. Setting the column to the right colation so do this.

3. Escaping characters
We can use ESCAPE to locate a wildcard character. EG: SELECT destn FROM order_process
where destn like ('-------\_%') ESCAPE '\'

4. Which characters need to be escaped (obvious ones are: string delimiter(s), wildcard character(s) and other delimiters (such as [ and ] in case of MS SQL Server)

5. How would the characters be escaped
Same as 3.

6. Is there a difference in escaping the characters when they are appearing in different context (e.g. IN vs. LIKE condition)
I think the Escape keyword only works with "Like".

7. Can the forbidden column/table (Eg: Title is a forbidden name) names be used by surrounding them with some character (e.g. []) or preceding it by some character (e.g. ')
In SQL Server we can use “[“ to mask the forbidden names. Eg: I will use [Name] for a column name which is called as NAME and it will work. My Database has some fields called TITLE and I need to change the name of the fields to create the table in Teradata. If I change the name, my code breaks. If I don’t, Teradata does not allow me to create the table. Whats the way out?

8. What should be the implementation of convert date for Teradata?
Select CONVERT(varchar(20),getdate(),101) whats the equivalent of this in Teradata. The DQL Server Function which I mentioned allows me to select date in different formats. The function will return me date in the format of: 12/17/2007 )
I need YYYY/MM/DD HH/SS

Any help would be greatly appreciated.
5 REPLIES
Junior Contributor

Re: Escape Characters, Operators & Date Conversions

1. Mapping of Text data type for Teradata
Long Varchar

*** This is equal to VarChar(64000), if there are more chars better use CLOB

2. Is varchar in Teradata Unicode compliant? If not, is there any Unicode compliant data type in Teradata?
Yes. Setting the column to the right colation so do this.

*** It's not the collation, but the character set

4. Which characters need to be escaped (obvious ones are: string delimiter(s), wildcard character(s) and other delimiters (such as [ and ] in case of MS SQL Server)

*** exactly three chars: wildcards "_" and "%" and the escape character itself
*** a single quote within a string is replaced by two quotes: 'bla''bla'

5. How would the characters be escaped
Same as 3.

*** Don't know exactly what you mean, "CHAR(10)"? That's 'A'xc

7. Can the forbidden column/table (Eg: Title is a forbidden name) names be used by surrounding them with some character (e.g. []) or preceding it by some character (e.g. ')
In SQL Server we can use “[“ to mask the forbidden names. Eg: I will use [Name] for a column name which is called as NAME and it will work. My Database has some fields called TITLE and I need to change the name of the fields to create the table in Teradata. If I change the name, my code breaks. If I don’t, Teradata does not allow me to create the table. Whats the way out?

*** In Teradata (SQL Server, too) you can use Standard SQL: double quotes -> "TITLE"

8. What should be the implementation of convert date for Teradata?
Select CONVERT(varchar(20),getdate(),101) whats the equivalent of this in Teradata. The DQL Server Function which I mentioned allows me to select date in different formats. The function will return me date in the format of: 12/17/2007 )
I need YYYY/MM/DD HH/SS

*** Dates are formatted on the client side. If you want Teradata to return a formatted output you have to cast it to a string and apply a FORMAT, e.g.
select cast((current_timestamp (format 'yyyy-mm-ddBhh:mi')) as char(16))
Check the "Datatypes and Literals" manual for valid formats.

Caution: In Teradata there's DATE, TIME, TIME WITH TIME ZONE, TIMESTAMP and TIMESTAMP WITH TIME ZONE.

Dieter
Enthusiast

Re: Escape Characters, Operators & Date Conversions

Thanks for the reply. That helped in clearing my doubts.
Enthusiast

Re: Escape Characters, Operators & Date Conversions

I have a query as
select fname from employee where fname like '%'

If I have an employee whos name contains how can I escape it? I have tried
select fname from employee where fname like '%' escape('%')
but it gave me an error saying "3998: The Pattern String for LIKE has an invalid ESCAPE Sequence.
Output directed to Answerset window"

Does that mean that I cannot escape the wild card characters (point # 4)
Junior Contributor

Re: Escape Characters, Operators & Date Conversions

You place the escape character before the searched char.
Any fname with a "%":
fname like '%\%%' escape '\'

A tablename with two "_":
select * from dbc.tables
where trim(tablename) like '%\_%\_%' escape '\'

Dieter
Enthusiast

Re: Escape Characters, Operators & Date Conversions

Thanks dnoeth. Appreciate the time you spent in replying my queries.