All Forums Database
u156531 6 posts Joined 10/14
19 Mar 2015
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

dnoeth 4628 posts Joined 11/04
22 Mar 2015

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.


cloghin 17 posts Joined 04/12
29 May 2015

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. 

Fred 1096 posts Joined 08/04
03 Jun 2015

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.

You must sign in to leave a comment.