Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

String concatenation...

Status
Not open for further replies.

billandmiss

IS-IT--Management
Nov 8, 2005
3
Does anyone have a simple example of doing this in Teradata SQL?

I want to concatenate 'Test Text' & MyFieldName

Thanks
 
you use Double OR Bars

||

to do concat..

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

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

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

Code:
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.



Code:
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.
 
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.

Code:
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...
 
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.
 
OOOPS,[hammer]

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'.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top