All Forums UDA
sparan 19 posts Joined 05/06
05 Feb 2008
Check newline character in a text column

Guys,can any one let me know how to check the newline character in a given varchar/char field in teradata?ex:i have a text column in a teradata table which can hold 10 characters. I need to check if there is any newline() exists in that field.Thanks,Sparan

joedsilva 505 posts Joined 07/05
05 Feb 2008

SELECT * FROM MyTableWHERE MyCharCol LIKE '%' || x'0A' || '%';

sparan 19 posts Joined 05/06
06 Feb 2008

Thanks a lot! It works for me.Can you tell me what is x'OA' is? Is it a hexadecimal value for new line character?Thanks,

joedsilva 505 posts Joined 07/05
06 Feb 2008

That's correct, it's the same as the ASCII value for newline in hex.

sparan 19 posts Joined 05/06
06 Feb 2008

it would be great if you can send doc/link to get the equivalent HEX for all the invisible characters ( like \r,back space,tab ...etc).Again thanks a lot for your help.

joedsilva 505 posts Joined 07/05
06 Feb 2008

that's only a bit of googling awayhere's one ......http://www.asciitable.com/

ojhapankaj 1 post Joined 08/13
09 Aug 2013

Hi,
I am having same issue of new line character within data in teradata.
How to replace new line character with space using any teradata select query?
Thank you

kharthigeyan 2 posts Joined 10/10
09 Jul 2014

Yes you can replace NEWLINE using the OREPLACE function in SELECT.
--removing \r\n
SEL OREPLACE(OREPLACE(yourcolumn , X'0d' , ' ') , X'0a' , ' ') yourcolumn
FROM yourdb.yourtable;
--removing \n
SEL OREPLACE(yourcolumn , X'0a' , ' ') yourcolumn
FROM yourdb.yourtable;

You must sign in to leave a comment.