New line character in varchar field
Candy Lam
I know that in isql one can add one address by changing the string
to :
'string' + X'0A' + X'0D'
However, when I tried to upload a file to table, it takes the characters
literally.
Can anybody offer any hint?
Steve McDaniel
Are you asking how to remove embedded newlines and carriage returns?
If so, if this is a Unix system, I'd suggest using 'sed' or something like
that to remove the newlines/carriage returns before loading into the database.
Steve
At 10:57 AM 7/20/99 +0100, Candy Lam wrote:
>
>--Boundary_(ID_dsyvOetZ6madXeIlOJg1Bg)
>Content-type: text/plain; charset=us-ascii
>Content-transfer-encoding: 7bit
>--Boundary_(ID_dsyvOetZ6madXeIlOJg1Bg)
>Content-type: text/html; charset=us-ascii
>Content-transfer-encoding: 7bit
>
><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
><HTML>
>I'd wonder if anybody had this problem before. I'm attempt to upload
>an address file into a varchar field in a table. The data itself
>consists of new line and carriage return character.
><P>I know that in isql one can add <B>one</B> address by changing the string
>to :
><BR> 'string' + X'0A' + X'0D'
><BR>However, when I tried to upload a file to table, it takes the characters
>literally.
><P>Can anybody offer any hint?</HTML>
>
>--Boundary_(ID_dsyvOetZ6madXeIlOJg1Bg)--
>
+--------------------------------------------------------------------------+
| Steve McDaniel | |
| Corporate Database Manager | E-Mail: Steve.M...@cmdg.com |
| CMD Group | Voice: (770) 417-4201 |
| 30 Technology Parkway South | Fax: (770) 797-2010 |
| Suite 100 | Cell: (770) 331-1587 |
| Norcross, GA 30092 | |
+--------------------------------------------------------------------------+
____________________________________________________________________________
Notice: This message contains privileged and confidential information
intended only for the use of the addressee named above. If you are not
the intended recipient of this message, you are hereby notified that
you should not disseminate, copy or take any action in reliance on it.
If you have received this message in error please notify the CMD Group
in Atlanta, Georgia, USA on (770) 417-4000.
Martin Bowes
Hope this helps...
The critical thing here is to have a good delimiter for the end of the
address field. ie as you're already using newlines and may allow tabs in an
address. If the input file doesnt have them that'll make life a lot harder
and you may have to resort to a file processing script for awk or Perl to
help.
As an example, consider a file of names and addresses like:
a frog<TAB>13 the lilly pad
in the swamp
by the tree
*
a drover<TAB>bury me deep down below
where the dingoes and crows cant molest me
in the land where good drovers go
*
...End of File...
Note that we have two fields. A name field delimited by a tab character and an
address field spread over several lines delimited by an '*'. Each row is
terminated with a newline.
This can be loaded into a simple table with:
copy table address_test(
name=c0tab,
address='c0*',
dummy=d0nl
) from 'dummy.dat'
The result being:
Executing . . .
select * from address_test
+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|name |address |
+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|a frog |13 the lilly pad\nin the swamp\nby the tree\n |
|a drover |bury me deep down below\nwhere the dingoes and crows cant molest me\nin the land where good drovers go\n |
+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
(2 rows)
continue
Your SQL statement(s) have been committed.
Best of luck,
Martin Bowes
--
Random Duckman Quote #4:
Cornfed - You're aware that while it affords one the momentary
illusion of satisfaction, the spewing of bile is
never a permanent solution.