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 infofrom book_report where info like '%"OD0A"xc%'But it did not return any output. Please help.Thanks,Nishi
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.
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.
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
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.
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).
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.
As to the request for a change to support alternative EOR, there are no plans to support that, even if requested.
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
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.)
Yes, I'd like to use ^Z as field delimeter and 0x00 as row delimeter to construct the flat file as intermediate media to move data between external systems (HDFS and NFS) and Teradata. SQL Server bcp utility allows users to define both -t and -r It will be very cool to see similar option in TD.
Teradata may never support this feature, but I believe a lot of organizations are looking for suh option.