Check newline character in a text column

UDA
Enthusiast

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
7 REPLIES
Enthusiast

Re: Check newline character in a text column

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

Enthusiast

Re: Check newline character in a text column

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,
Enthusiast

Re: Check newline character in a text column

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

Re: Check newline character in a text column

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.

Enthusiast

Re: Check newline character in a text column

that's only a bit of googling away

here's one ......

http://www.asciitable.com/

Re: Check newline character in a text column

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

Re: Check newline character in a text column

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;