logo
  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

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

021315_1635_Concatenati1.png

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

021315_1635_Concatenati2.png

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

021315_1635_Concatenati3.png

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

021315_1635_Concatenati4.png

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 

021315_1635_Concatenati5.png

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…
021315_1635_Concatenati6.png

… 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

021315_1635_Concatenati7.png

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

Related

Tags: SQL, Strings, Teradata, Text Manipulation

2 comments

  • Mahdi July 26, 2016 at 5:23 pm - Reply

    Thanks

  • gizel July 19, 2019 at 6:22 pm - Reply

    Not clear what is CHAR (20)

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Showing a Picture Based on the Value of another Cell in Excel
Using SUMPRODUCT to create a Conditional Weighted Average in Excel
Showing Data Bars as a 'Proportion to Total' in Excel
Format or Remove Borders from a Slicer or Timeline in Excel
Using the same Named Range in multiple worksheets, but with different values, in Excel
Populate a Dropdown Based on the Value of Another Dropdown in PowerApps

Meta

  • Register
  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Categories

Pages

  • Excel Charts
  • About David Aldred & This Blog
  • Consulting & Excel Support

Copyright System Secrets 2021 - Theme by ThemeinProgress