new line character

Tools
Enthusiast

new line character

Hello all,
It seems like there is a new line character in one of my data columns; I noticed this when I downloaded a table to my Unix server where the record was split into 2 lines. How can I search for new line character in a table and if possible replace that new line character to a blank character?

I tried below stmt to search for new line character:
select info
from book_report where info like '%"OD0A"xc%'

But it did not return any output. Please help.

Thanks,
Nishi
14 REPLIES
Teradata Employee

Re: new line character

If you are using V2R6 then you can use the "WITH RECURSIVE" clause to update the character.

Other option is to process the data in a file using perl or awk.

Teradata Employee

Re: new line character

If you are going to use LIKE to find rows with LF, the syntax would be
LIKE '%'||'0A'xc||'%'

The "oreplace" UDF (included in the "Oracle-style functions" download from Teradata.com) or something similar is probably the simplest way to change such characters to spaces.
Enthusiast

Re: new line character

Hi,
This is the simplest query which will give you the result of all rows having mutiple line in column:

sel * from where like '%__
_____%'

Regds,
Sachin
Enthusiast

Re: new line character

Is there anyway to tell FastLoad, FastExport and TPT to use other special character as record delimiter? All Teradata utilities use \n in Unix and \n\r in Windows as the record delimiter.

Teradata Employee

Re: new line character

No. Those are defined record format definitions.

However, you can try and use the "unformatted" record format type where our tools will just read in the data according to the schema layout, and just put a CHAR(n) in the schema to account for the end-of-record marker (where 'n' is the number of bytes for your customized end-of-record marker).

-- SteveF
Teradata Employee

Re: new line character

What do you mean, it is not variable length?

Sure it is.

It uses the schema to read the data, field-by-field.

In other words, if your schema had INTEGER, CHAR(10), VARCHAR(100), the utility will read 4 bytes, and then 10 bytes and then 2 bytes (for the varchar field length, 'n'), followed by 'n' bytes for the varchar data.

And if you have a 3-byte end-of-record marker, then put CHAR(3) as the last "column" in the schema.

-- SteveF
Teradata Employee

Re: new line character

As to the request for a change to support alternative EOR, there are no plans to support that, even if requested.

-- SteveF
Enthusiast

Re: new line character

Sorry, NOFORMAT is variable length. What I meant to say is that decoding binary format of decimal, unicode string and timestamp is quite an effort.

The reason to avoid TD binary file format is to build a framework which can reuse the flat file format for TPT import & export, and copy the file into HDFS without modifying the content. It is possible to create a deserialization code for TD binary format, but it will make life a lot easier to support alternative record delimeter such as \x00 or \x0000

Teradata Employee

Re: new line character

You never mentioned what record format you WANT to use.

You only commented on the record delimiter.

Are you trying to use "delimited" format?

(Even if you are, there are still no plans to support a different EOR.)

-- SteveF