Removing a line break character in a column

Database
Enthusiast

Removing a line break character in a column

Hello, I have a problem with a field in my data where the field seems to contain a line break or a carriage return. I'm trying to remove this with the folloiwing select statement oreplace (cust_id, '0A'XC, '')as custid. It seems to be working but using the Oreplace function for any other value in this field also seems to get rid of the line break so I'm not certain if I'm using the correct method. Does anyone know if this is the correct way to  remove the line break? 

Thanks, Mark

3 REPLIES
Senior Apprentice

Re: Removing a line break character in a column

Hi Mark,

oReplace does not automatically replace line breaks, check with CHAR2HEXINT. 

But as you must remove both CR and LB you better use oTranslate(x, '0A0D'xc,'') instead.

Enthusiast

Re: Removing a line break character in a column

TD_SYSFNLIB.OTRANSLATE() is documented to return the character set of the source_string argument.  This is normal , if LATIN is input, Latin should be output and same for unicode. 

However, on TD14.10 this fast-path function only returns VARCHAR(8000) UNICODE. This causes issues be exceeding the 64 KB row length because when applied on all varchar columns they triple their size as they become unicode. 

Is this a bug on OTRANSLATE and is there an efficient way to get the correct behavior? My goal is to remove the field and row delimiters from all character based columns for an all fields table export in delimited format. 

Thanks. 

Teradata Employee

Re: Removing a line break character in a column

Unfortunately oTranslate returns LATIN only if all three arguments are LATIN. Otherwise the LATIN arguments are implicitly translated to UNICODE and the result also becomes UNICODE. And literal strings are always UNICODE.

Workaround is to explicitly TRANSLATE('any literal' using Unicode_to_Latin) in the argument list.