String concatenation...

[billandmiss]

Does anyone have a simple example of doing this in Teradata SQL?

I want to concatenate 'Test Text' & MyFieldName

Thanks

 

[tdatgod]

you use Double OR Bars

||

to do concat..

This is the same as the 'C' Language Logical OR Operator.

   sel 'Test Text ' || MyFieldName as mytext
      from MyTable;

Make sure to add proper spacing. Without it it all runs together.

sel 'This'  || 'is' || 'a' || 'test.' as mytext;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

mytext
------------
Thisisatest.

I added the

As mytext

to give the resulting column a name because without it Teradata just uses the concatenation as seen here as the column header.

sel 'This '  || 'is ' || 'a ' || 'test.' ;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

((('This '||'is ')||'a ')||'test.')
-----------------------------------
This is a test.

 

[Rhinohd]

Hello,
I too am having issues doing this in SQL assistant. I am trying to concatenate 5 fields into one for my answer set. I keep getting a strange result.

SELECT field_1, (field_2||field_3||field_4||field_5||field_6), field_7, count(*) cnt

My result looks like this
836 B7 1. 2. 3. 236. 20 781

I need it to look like this
836 B7123236 20 781

What am I doing wrong? Thanks in advance...

 

[al1x24]

Hi,

I suppose your field_3..6 are defined as DECIMAL or NUMERIC.
You have to FORMAT these values before concatenation.

trim((FIELD_n (format 'Z(I)')) will answer your problem in a generic way.

If you are sure aboout the length of your field, you can use a simpler (FIELD_n (format 'Z(x)')), where x is the length of the value.

 

[al1x24]

OOOPS,

I was too fast in my answer.
If your value is 0, (FIELD_n (format 'Z(x)')) will return an empty string.
Use trim(FIELD_n (format 'Z(I)9')) to be sure to get a '0'.
Use (FIELD_n (format '9(x)')) when you want a fixed length completed left with '0'.

 

[Rhinohd]

Thank You very much!!! Worked like a charm...