Very Computer

by **thomas.haan..** » Sat, 21 Apr 2001 21:11:25

Hi All

I'm using informix 7.31 on an AIX 4.3.3 and have a slight problem removing
newline charcters from SQL

Assume we have a tuple consisting of one string field:

"frist line^Msecond_line^Mthird_line^M"

Does anyone know how to remove newline charcters from such a string
(newline character could be created either from UNIX or Windows).

I think that i should probably use the replace function, but i can't get it
to match
any of the following:
%n
\n
^M
ascii(13)

I would prefere a solution that doesn't use PL/SQL or stored procedures.

Best Regards Thomas

by **Lambros Papadopoulo** » Sat, 21 Apr 2001 23:36:53

I found no way of defining a literal newline character either, so I read it
from a table. The most convinient table is sysprocbody.

In the following sql :

select
replace(data, (select data[69,70] from sysprocbody where datakey = 'T'
and procid = 1 and seqno = 1)),
data
from sysprocbody where datakey = 'T' and procid = 1;

the replace function removes CR/LF from the body of procedure systdist
(procid = 1). Mine is an NT system, on unix you may have to remove only LF.
Just make sure that you locate the LF in the first line of the procedure (in
my the first case of CR/LF was at line 1 positions 69 and 70).

Regards

Quote:
> Hi All

> I'm using informix 7.31 on an AIX 4.3.3 and have a slight problem removing
> newline charcters from SQL

> Assume we have a tuple consisting of one string field:

> "frist line^Msecond_line^Mthird_line^M"

> Does anyone know how to remove newline charcters from such a string
> (newline character could be created either from UNIX or Windows).

> I think that i should probably use the replace function, but i can't get
it
> to match
> any of the following:
> %n
> \n
> ^M
> ascii(13)

> I would prefere a solution that doesn't use PL/SQL or stored procedures.

> Best Regards Thomas

by **Frank Wout** » Tue, 24 Apr 2001 22:25:34

You can use the unix tool dos2unix <infile> <outfile>
Problem solved?
It's hard to globally replace an EOL character within vi, at least
until now I never succedded doing that, if you do, I'd like to know how.
Greetings,
Frank Wouts
Informa Automatisering B.V.
The Netherlands

> Hi All

> I'm using informix 7.31 on an AIX 4.3.3 and have a slight problem removing
> newline charcters from SQL

> Assume we have a tuple consisting of one string field:

> "frist line^Msecond_line^Mthird_line^M"

> Does anyone know how to remove newline charcters from such a string
> (newline character could be created either from UNIX or Windows).

> I think that i should probably use the replace function, but i can't get it
> to match
> any of the following:
> %n
> \n
> ^M
> ascii(13)

> I would prefere a solution that doesn't use PL/SQL or stored procedures.

> Best Regards Thomas

by **Kevin_Dews..** » Wed, 25 Apr 2001 05:42:57

Try searching for Ctrl+v+m

cc: (bcc: Kevin Dewsnup/AGInc)
Subject: Re: removing newline charcter in SQL

Note: Some recipients have been dropped due to syntax errors.
Please refer to the "$AdditionalHeaders" item for the complete headers.

You can use the unix tool dos2unix <infile> <outfile>
Problem solved?
It's hard to globally replace an EOL character within vi, at least
until now I never succedded doing that, if you do, I'd like to know how.
Greetings,
Frank Wouts
Informa Automatisering B.V.
The Netherlands

> Hi All

> I'm using informix 7.31 on an AIX 4.3.3 and have a slight problem
removing
> newline charcters from SQL

> Assume we have a tuple consisting of one string field:

> "frist line^Msecond_line^Mthird_line^M"

> Does anyone know how to remove newline charcters from such a string
> (newline character could be created either from UNIX or Windows).

> I think that i should probably use the replace function, but i can't get
it
> to match
> any of the following:
> %n
> \n
> ^M
> ascii(13)

> I would prefere a solution that doesn't use PL/SQL or stored procedures.

> Best Regards Thomas

by **Cesar Ivan Cruz Romer** » Wed, 25 Apr 2001 06:56:00

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C0CC40.2EC639D0
Content-Type: text/plain

1.- edit file

vi file-name

2.- remove ^M from all lines in file ( use ^V and ^M for see it in vi )
:1,$s/^M//g

3.- put ^M

:1,$s/$/^M/g

> -----Mensaje original-----

> Enviado el: Lunes 23 de Abril de 2001 7:26 AM

> Asunto: Re: removing newline charcter in SQL

> You can use the unix tool dos2unix <infile> <outfile>
> Problem solved?
> It's hard to globally replace an EOL character within vi, at least
> until now I never succedded doing that, if you do, I'd like to know how.
> Greetings,
> Frank Wouts
> Informa Automatisering B.V.
> The Netherlands

> > Hi All

> > I'm using informix 7.31 on an AIX 4.3.3 and have a slight problem
> removing
> > newline charcters from SQL

> > Assume we have a tuple consisting of one string field:

> > "frist line^Msecond_line^Mthird_line^M"

> > Does anyone know how to remove newline charcters from such a string
> > (newline character could be created either from UNIX or Windows).

> > I think that i should probably use the replace function, but i can't get
> it
> > to match
> > any of the following:
> > %n
> > \n
> > ^M
> > ascii(13)

> > I would prefere a solution that doesn't use PL/SQL or stored procedures.

> > Best Regards Thomas

------_=_NextPart_001_01C0CC40.2EC639D0
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2653.12">
<TITLE>RE: removing newline charcter in SQL</TITLE>
</HEAD>
<BODY>

1.- edit file

vi file-name

2.- remove ^M from =
all lines in file ( use ^V and ^M for see it in vi )
 :1,$s/^M//g

3.- put ^M

:1,$s/$/^M/g

 
<UL>
-----Mensaje original-----
 De:     Enviado =
el:     Lunes 23 de Abril de 2001 7:26 AM
 Para:   Asunto: Re: removing newline charcter in SQL

You can use the unix tool dos2unix =
<infile> <outfile>
 Problem solved?
 It's hard to globally replace an EOL =
character within vi, at least
 until now I never succedded doing =
that, if you do, I'd like to know how.
 Greetings,
 Frank Wouts
 Informa Automatisering B.V.
 The Netherlands

> Hi All
 >
 > I'm using informix 7.31 on an =
AIX 4.3.3 and have a slight problem removing
 > newline charcters from =
SQL
 >
 > Assume we have a tuple =
consisting of one string field:
 >
 > "frist =
line^Msecond_line^Mthird_line^M"
 >
 > Does anyone know how to remove =
newline charcters from such a string
 > (newline character could be =
created either from UNIX or Windows).
 >
 > I think that i should probably =
use the replace function, but i can't get it
 > to match
 > any of the following:
 > %n
 > \n
 > ^M
 > ascii(13)
 >
 > I would prefere a solution that =
doesn't use PL/SQL or stored procedures.
 >
 > Best Regards Thomas

</UL>
</BODY>
</HTML>
------_=_NextPart_001_01C0CC40.2EC639D0--