cast and concatenate

Database
Enthusiast

cast and concatenate

In sybase this how I change data type and concatenate :

what is the equivalent in Teradata?

cast((cust_id || RIGHT('0' + CAST(cust_nbr AS varchar),2) || cust_dig) AS Bigint)

7 REPLIES
Junior Contributor

Re: cast and concatenate

CAST and || are both Standard SQL, so exactly the same in Teradata.

There's no RIGHT function in Teradata, but it's easy to rewrite:

RIGHT(col, n) = SUBSTRING(col FROM CHAR_LENGTH(col) - n + 1)

What are the datatypes of cust_id, cust_nbr and cust_dig, CHAR/VARCHAR/INT?

If they are numeric you might have to use TRIM(cust_id) or maybe add a FORMAT.

SUBSTRING('0' || cust_nbr FROM CHAR_LENGTH(cust_nbr) - 2 + 1)

Enthusiast

Re: cast and concatenate

they are all numeric

Enthusiast

Re: cast and concatenate

cust_id    cust_nbr   cust_dig

123            2            5

the newfiled which must be a numerica datatype  is 123025

Junior Contributor

Re: cast and concatenate

So you need some TRIMs:

CAST(TRIM(cust_id) || SUBSTRING('0' || TRIM(cust_nbr) FROM CHAR_LENGTH(TRIM(cust_nbr)) - 2 + 1) || TRIM(cust_dig) AS BIGINT)
Fan

Re: cast and concatenate

hi Dieter,

While running the below query, am getting the error like "SELECT Failed 3775, Invalid Hexadecimal Constant" .

sel * from dbc.databasespace where tableid= '00-00-1C-00-00-00'xb

Can you please help me out , how to retrive byte data ?

Thnaks,

Salokh

Junior Contributor

Re: cast and concatenate

Hi Salokh,

remove the dashes from the hex string:

sel * from dbc.databasespace where tableid= '00001C000000'xb
Fan

Re: cast and concatenate

Many thanks Dieter, its working fine now.