l i n u x - u s e r s - g r o u p - o f - d a v i s
Next Meeting:
April 21: Google Glass
Next Installfest:
Latest News:
Mar. 18: Google Glass at LUGOD's April meeting
Page last updated:
2004 Jun 07 16:50

The following is an archive of a post made to our 'vox-tech mailing list' by one of its subscribers.

Report this post as spam:

(Enter your email address)
RE: [vox-tech] Changing data with awk
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

RE: [vox-tech] Changing data with awk

Actually, replacing the data in SQL Server is quite easy.  You need to
search for the ASCII value, and then use the REPLACE function within a
query.  Make a view first.  You can then have a process that makes a
table from this view whenever you need to export(this should be
relatively easy in the DTS designer.

Here is the SQL:

SELECT     REPLACE(CAST(quote AS VARCHAR(7999)), CHAR(13), '@@') AS
FROM         NewQuotes
WHERE     (quote LIKE '%' + CHAR(13) + '%')

In this example, [quote] is a field in a table named [NewQuotes].

The original data looks like:
There stood a mixing bowl filled with ambrosia, while Hermes grasped the
pitcher to serve the gods.

The output data looks like:
There stood a mixing bowl filled with ambrosia, while Hermes grasped the
pitcher to serve the gods.@@ergfvserv@@sefrgse.

BTW you should also look for Char(10) which is the line feed, char (13)
is the carriage return.

One other thing, I used the CAST function because my original column was
ntext datatype, which cannot be used with the REPLACE function.

Everyone else, please don't flame me for suggesting a Microsoft solution
on this listserv.  He is starting out there, and this is the easiest way
to deal with the problem.

Let me know if I can help more.



Matthew Lange
721 Barcelona Ave.
Davis, CA  95616
voice:  916-275-1602
email:  infosophist@infosoph.com

-----Original Message-----
From: vox-tech-admin@lists.lugod.org
[mailto:vox-tech-admin@lists.lugod.org] On Behalf Of Richard Crawford
Sent: Monday, June 07, 2004 9:20 AM
To: vox-tech@lists.lugod.org
Subject: [vox-tech] Re: Changing data with awk

Apologies for breaking the thread.  I'm reading various responses on the
LUGOD website, since the e-mails are stuck on my computer at home, so
replying from SM.

Jay wrote, quoting Mark,

>> Yes, "\n" terminates a record.  But Richard (the original poster)
>> that the field has embedded "carriage return" characters, which is
>> Since "\r" is not "\n", the codes do work... at least under *NIX.
>> Yes, pressing the "Enter" key produces carriage return code on a
>> keyboard (ASCII 13 "\r"), but *NIX translates it to the linefeed code
>> (ASCII 10 "\n"), whereas DOS/Windows translates it to carriage return
>> followed by linefeed "\r\n", and the older MacOS (before 10) doesn't
>> translate it at all.  And by convention "\n" under C (along with
>> other languages) represents the default line terminator for that
>> ("\n" for *NIX, "\r\n" for DOS/Windows, "\r"  under older MacOS.)
>> unlike DOS/Windows, UNIX Lets you turn off the translation using
>> But now we're off topic... -_-v
>> -Mark

>Thanks Mark, I wasn't aware of those subtleties.
>But, I'm betting Richard has embedded ASCII 10s in his file, and that
>is why SQLLoader (Oracle's mass data loading tool, which uses ASCII 10s
>as it's record separator) is giving him problems.
>Richard, wha'chu'got in your file?

Here's the scoop.

I've got a moderately-sized table in our database (only 35,000 records)
which is part of our messaging system.  One of the fields, "msgMessage",
contains the text of e-mail messages sent from students to instructors,
and vice versa (don't question me about the logic of this approach; I
didn't design the system, I'm just stuck with it -- heh).  Since many of
these messages have carriage returns as line breaks, they're in the data
for this field for this table.

So I'm using a Windows program, DBArtisan, to export the table into an
external .DAT file, using the caret, ^, as the field separator.  I'm
SQL Loader to create the control file which I'll use to load the data
this .dat file into Oracle.  Unfortunately, while SQL Loader understands
the ^ field separator just fine, it interprets ALL carriage returns as
end-of-records, whether it really is at the end of the record or within
the msgMessage field.  Thus, the carriage returns in Field 6 are
interpreted as end-of-records.  That's something I didn't really think
through before.

You'd think that this would be a common problem, but I have yet to find
solution on the web or with any sort of tech support.  I wish there were
way to fix the data in the tables before exporting from SQL Server, but
there doesn't seem to be an easy way to do that.

Richard S. Crawford (AIM: Buffalo2K)
http://www.mossroot.com   http://www.stonegoose.com/catseyeview
"You cannot trust your judgement if your imagination is out of focus." 
--Mark Twain

vox-tech mailing list
vox-tech mailing list

LUGOD Group on LinkedIn
Sign up for LUGOD event announcements
Your email address:
LUGOD Group on Facebook
'Like' LUGOD on Facebook:

Hosting provided by:
Sunset Systems
Sunset Systems offers preconfigured Linux systems, remote system administration and custom software development.

LUGOD: Linux Users' Group of Davis
PO Box 2082, Davis, CA 95617
Contact Us

LUGOD is a 501(c)7 non-profit organization
based in Davis, California
and serving the Sacramento area.
"Linux" is a trademark of Linus Torvalds.

Sponsored in part by:
O'Reilly and Associates
For numerous book donations.