All Forums Tools
nishi 8 posts Joined 11/06
13 Feb 2007
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 infofrom book_report where info like '%"OD0A"xc%'But it did not return any output. Please help.Thanks,Nishi

j355ga 100 posts Joined 12/05
16 Feb 2007

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.

Jeff

Fred 1096 posts Joined 08/04
17 Feb 2007

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.

sachinp17 53 posts Joined 11/06
19 Feb 2007

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

ericsun2 44 posts Joined 06/10
30 Jul 2012

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.

feinholz 1234 posts Joined 05/08
30 Jul 2012

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

feinholz 1234 posts Joined 05/08
30 Jul 2012

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

feinholz 1234 posts Joined 05/08
30 Jul 2012

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

 

--SteveF

ericsun2 44 posts Joined 06/10
30 Jul 2012

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

feinholz 1234 posts Joined 05/08
30 Jul 2012

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

ericsun2 44 posts Joined 06/10
30 Jul 2012

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.

Ekladios 13 posts Joined 02/12
23 Jan 2014

Is there a way  to replace up to 10 new-line characters in one table column with spaces other than using Oreplace?
I tried complex substring to replace up to 4 NL after that it got messy

Raja_KT 1246 posts Joined 07/09
23 Jan 2014

You have not given an example and what you expect :)
Maybe you can try with regex_replace:

SELECT REGEXP_REPLACE('it is the first line

it is the third line

2.it is the second line ', CHR (20), '*')

 

 

Cheers,

 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
24 Jan 2014

@Raja:
I assume Ekladios wanted to avoid oReplace because it's not installed on his system.
If there's no oReplace the probability to for a RegExp function is almost zero :-)
 
@Ekladios: 
I assume your system is pre-TD14.
IMHO each system should have at least the oReplace and oTranslate functions installed. Some DBAs are very reluctant regarding UDFs, so you must should request them over and over again.
Ask your DBA how to write this query. And then show how easy it is with a simple SELECT oTranslate(col, '0D0A'xc,').
Those functions are used at many customer sites for years, TD14 finally includes them.
 

Dieter

Raja_KT 1246 posts Joined 07/09
24 Jan 2014

oh! I wish all clients upgrade to 14.10 with all features. :)
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.