I have a CHAR(12) field in a table. This field is a last 12 month field, so it provides 12 characters representing each of the past 12 month. Problem is, the 12 characters in this field could be blank. So when I go to export, it gets all messed up. Is there a way to look into a field and convert any blanks into a value; say; x ?
Field will look like this:
'1 12 1 1'
' 11 '
I want to see this:
1. This can be achieved by OREPLACE function in TD, the equivalent of the Oracle funtion.
Query : select oreplace(colname,' ','x') from Table; -- (' ' is space)
This function actually only supplies the output of 'x' up to the last real digit. It's no problem, but the out put is more like:
What's your Teradata release?
This works fine for me, but you better use oTranslate instead of oReplace:
SELECT CAST(OTRANSLATE('1 12 ', ' ', 'x') AS CHAR(12))
oReplace is built-in since TD14, if you're on an older release or got a UDF with the same name this might work differently.