Concatenating Strings in Teradata – And What to Watch Out for with Fixed Length Fields

February 13, 2015 2 comments Article Intermediate, SQL, Teradata

Concatenating Strings in Teradata is fairly straightforward, but there are a few things you need to watch out for.

[Teradata]

Every once in a while, it’s necessary to concatenate strings together to create a new field. Nothing particularly difficult there; Teradata lets us use the Double Pipe delimiter ( || ) to join strings.

Example:

SELECT 'David' || ' J ' ||  'Aldred' AS Fullname

However, there are a few watch outs you should familiarise yourself with
1. Firstly, you can’t concatenate with a NULL field. Well, you could try, but you will of course get NULL back as your answer

SELECT 'David' || NULL ||  'Aldred' AS Fullname

To get around this, you can wrap each field in a CASE statement to remove/replace the NULL value

SELECT CASE WHEN 'David' IS NULL THEN '' ELSE 'David' END || CASE WHEN NULL IS NULL THEN ' ' ELSE ' J ' END || CASE WHEN 'Aldred' IS NULL THEN '' ELSE 'Aldred' END AS Fullname

However, doing this opens up another quirk. If you are working with Fixed Length fields, you may have trailing whitespace.
When you so a standard concatenation, this whitespace is maintained.

SELECT Brand || ' - ' || Colour || ' - ' || Descrip
FROM   (SELECT  CAST('Product Brand' AS CHAR(20)) AS Brand,  CAST('Colour' AS CHAR(20)) AS Colour, CAST('Product Description' AS CHAR(20)) AS Descrip) q1

Of course, if you don’t want to see that extra whitespace, then of course you can remove it via a TRIM function

SELECT TRIM(Brand) || ' - ' || TRIM(Colour) || ' - ' || TRIM(Descrip)
FROM   (SELECT  CAST('Product Brand' AS CHAR(20)) AS Brand,  CAST('Colour' AS CHAR(20)) AS Colour, CAST('Product Description' AS CHAR(20)) AS Descrip) q1

But… what if you have fixed length fields, but need to ignore NULL values?
You would have a statement like this…

  SELECT CASE WHEN Brand IS NULL THEN '' ELSE Brand END || ' - ' ||
  	   CASE WHEN Colour IS NULL THEN '' ELSE Colour END || ' - ' ||
  	   CASE WHEN Descrip IS NULL THEN '' ELSE Descrip END AS BrandDesc

  FROM   (SELECT  CAST('Product Brand' AS CHAR(20)) AS Brand,  CAST('Colour' AS CHAR(20)) AS Colour, CAST('Product Description' AS CHAR(20)) AS Descrip) q1

But looks what happens…

… We’ve lost our whitespace. The query is re-casting the values as part of the CASE statement, but as VARCHAR
Of course, you might be happy with this effect – but there are occasions where you want to keep the padding exactly how it was. It’s a pain, but can be reverted by re-casting our values.

SELECT CASE WHEN Brand    IS NULL THEN CAST('' AS CHAR(20))  ELSE CAST(Brand AS CHAR(20)) END || ' - ' ||
	CASE WHEN Colour   IS NULL THEN CAST('' AS CHAR(20)) ELSE CAST(Colour AS CHAR(20)) END || ' - ' ||
	CASE WHEN Descrip IS NULL THEN CAST('' AS CHAR(20)) ELSE CAST(Descrip AS CHAR(20)) END AS BrandDesc

  FROM   (SELECT  CAST('Product Brand' AS CHAR(20)) AS Brand,  CAST('Colour' AS CHAR(20)) AS Colour, CAST('Product Description' AS CHAR(20)) AS Descrip) q1

Note, however, that I’ve also CAST the empty response

CAST('' AS CHAR(20))

This is important, and you data will not come back spaced otherwise.
There you have it. There aren’t going to be many occasions where you need to concatenate fixed length fields where you need to maintain any whitespace, and force a NULL replacement – but at least there’s a way around it!

David