Sql Replacing a char
A user needed to replace embedded newline characters in a char/varchar column (about 100,000 of 156 million rows) using an UPDATE, but couldn't figure out how to pass a newline into the REPLACE() function. Suggestions included the regexp DataBlade and a hand-written SPL searchReplace() function. The simple working answer: run EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('t'); then use REPLACE(desc, '<literal Enter>', '#') — typing an actual newline inside the quoted string (in DBaccess, ^V^M also works). Jonathan Leffler noted an ASCII(10) function can generate the LF instead.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
To all: We have a table with 156 million rows. On approx 100,000 records we need to replace a NewLine Character in one field {type char(50)} with some other character. Is there some function that can do that within an update statement. I found the replace function {replace (string,x,y)} but I have not found a way to pass into it the new-line character. Any help would be appreciated. Stephen Scott
Hi Stephen, You should able to use the replace() for your requirement. By "NewLine" do you mean a "^M" character? For a better understanding can you give an example what you are trying to? Thanks, Sanjit
If you are on V9+ then I'd look at the regexp datablade Paul Watson Tel: +44 1414161772 +1 913-400-2620 Mob: +44 7818003457 +1 913-636-2858 Web: www.oninit.com Failure is not as frightening as regret. Attend IDUG 2007 San Jose, North America May 6-10, 2007 Visit http://www.iiug.org/conf for more information. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of STEPHEN SCOTT > Sent: 10 April 2007 17:14 > To: ids@iiug.org > Subject: Sql Replacing a char [8811] > > To all: > > We have a table with 156 million rows. On approx 100,000 > records we need to replace a NewLine Character in one field > {type char(50)} with some other character. Is there some > function that can do that within an update statement. > > I found the replace function {replace (string,x,y)} but I > have not found a way to pass into it the new-line character. > > Any help would be appreciated. > > Stephen Scott > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the > discussion forum.
Here is the table structure:
create table hist_podesc (
po_num INT not null,
orderedby_yard SMALLINT,
sku INT not null,
seq SMALLINT not null,
desc VARCHAR(55),
sequence SMALLINT,
price DECIMAL(9,3)
);
EXAMPLE DATA
po_num = 90010564
orderedby_yard = 3011
sku = 2712406
seq = 7
desc = "Taupe.
Packaging instructions:
Pack 4 rockers in a"
sequence = 11464
price = 1.12
I would need to replace the desc field to replace the newline with a "#" so it
would look like the following:
"Taupe.#Packaging instructions:#Pack 4 rockers in a"
I tried
update hist_podesc set desc = replace(desc,"\\\\^M" ,"#") where po_num = 90010564
That didn't work. Is there any way to pass the ASCII value into that function.
Stephen
You didn't mention the version you are on - and I'm not sure which
version it was implemented, but IFX_ALLOW_NEWLINE helped me with a
similar problem.
In dbaccess:
EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('t');
select replace( your_column, "^M", "X" )
from your_table;
In dbaccess I used ^V^M to get the ^M.
Patrick McDonough
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Paul Watson
Sent: Tuesday, April 10, 2007 10:30 PM
To: ids@iiug.org
Subject: RE: Sql Replacing a char [8814]
If you are on V9+ then I'd look at the regexp datablade
Paul Watson
Tel: +44 1414161772 +1 913-400-2620
Mob: +44 7818003457 +1 913-636-2858
Web: www.oninit.com
Failure is not as frightening as regret.
Attend IDUG 2007 San Jose, North America
May 6-10, 2007
Visit http://www.iiug.org/conf for more information.
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> Behalf Of STEPHEN SCOTT
> Sent: 10 April 2007 17:14
> To: ids@iiug.org
> Subject: Sql Replacing a char [8811]
>
> To all:
>
> We have a table with 156 million rows. On approx 100,000
> records we need to replace a NewLine Character in one field
> {type char(50)} with some other character. Is there some
> function that can do that within an update statement.
>
> I found the replace function {replace (string,x,y)} but I
> have not found a way to pass into it the new-line character.
>
> Any help would be appreciated.
>
> Stephen Scott
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the
> discussion forum.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Steve,
Below is a search and replace function I wrote for some special
replacements. I tested it with your table and sample data to make sure it
worked for the newline character and it did. You will need to change the
function to a procedure if you are on a 7.x version.
----------------------------------------------------------------------------
-
DROP FUNCTION searchReplace( VARCHAR, VARCHAR, VARCHAR );
CREATE FUNCTION searchReplace( iVcString VARCHAR( 255 ),
iVcSearchString VARCHAR( 255 ),
iVcReplaceString VARCHAR( 255 ) )
RETURNING VARCHAR( 255 );
DEFINE oVcString VARCHAR( 255 );
DEFINE lVcStringMatch VARCHAR( 255 );
DEFINE lSiIndex SMALLINT;
DEFINE lSiStringLength SMALLINT;
DEFINE lSiStringLengthReplace SMALLINT;
DEFINE lSiStringLengthSearch SMALLINT;
LET lSiStringLength = LENGTH( iVcString );
LET lSiStringLengthSearch = LENGTH( iVcSearchString );
LET lSiStringLengthReplace = LENGTH( iVcReplaceString );
LET oVcString = "";
LET lSiIndex = 1;
WHILE ( lSiIndex <= lSiStringLength )
IF ( SUBSTR( iVcString, lSiIndex, lSiStringLengthSearch ) =
iVcSearchString ) THEN
LET oVcString = oVcString || iVcReplaceString;
IF ( lSiStringLengthReplace = 0 ) THEN
LET lSiIndex = lSiIndex + 1;
ELSE
LET lSiIndex = lSiIndex + lSiStringLengthSearch;
END IF;
ELSE
LET oVcString = oVcString || SUBSTR( iVcString, lSiIndex, 1
);
LET lSiIndex = lSiIndex + 1;
END IF;
END WHILE;
RETURN oVcString;
END FUNCTION;
----------------------------------------------------------------------------
-
I then used the following to update:
update hist_podesc set desc = searchReplace( desc, '', '#' );
Yes, I hit enter for the newline instead of putting in a special character.
Lennie
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
STEPHEN SCOTT
Sent: Wednesday, April 11, 2007 3:06 PM
To: ids@iiug.org
Subject: Re: Sql Replacing a char [8840]
Here is the table structure:
create table hist_podesc (
po_num INT not null,
orderedby_yard SMALLINT,
sku INT not null,
seq SMALLINT not null,
desc VARCHAR(55),
sequence SMALLINT,
price DECIMAL(9,3)
);
EXAMPLE DATA
po_num = 90010564
orderedby_yard = 3011
sku = 2712406
seq = 7
desc = "Taupe.
Packaging instructions:
Pack 4 rockers in a"
sequence = 11464
price = 1.12
I would need to replace the desc field to replace the newline with a "#" so
it
would look like the following:
"Taupe.#Packaging instructions:#Pack 4 rockers in a"
I tried
update hist_podesc set desc = replace(desc,"\\\\^M" ,"#") where po_num =90010564
That didn't work. Is there any way to pass the ASCII value into that
function.
Stephen
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
HI, Jarratt, Steve and Folks,
Looks we have a simple way to do it!
EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('t');
update hist_podesc set desc=replace(desc,'
',"#")
where po_num =90010564;
Yes, jusr hit Enter key, instead of using special cracter for NewLine.
Cheers!
Frank
On 4/11/07, Jarratt, Linwood B. <LJarratt@co.lake.il.us> wrote:
>
> Steve,
>
> Below is a search and replace function I wrote for some special
> replacements. I tested it with your table and sample data to make sure it
> worked for the newline character and it did. You will need to change the
> function to a procedure if you are on a 7.x version.
>
>
> ----------------------------------------------------------------------------
> -
> DROP FUNCTION searchReplace( VARCHAR, VARCHAR, VARCHAR );>
> CREATE FUNCTION searchReplace( iVcString VARCHAR( 255 ),>
> iVcSearchString VARCHAR( 255 ),
>
> iVcReplaceString VARCHAR( 255 ) )
> RETURNING VARCHAR( 255 );
>
> DEFINE oVcString VARCHAR( 255 );
> DEFINE lVcStringMatch VARCHAR( 255 );
> DEFINE lSiIndex SMALLINT;
> DEFINE lSiStringLength SMALLINT;
> DEFINE lSiStringLengthReplace SMALLINT;
> DEFINE lSiStringLengthSearch SMALLINT;
>
> LET lSiStringLength = LENGTH( iVcString );
> LET lSiStringLengthSearch = LENGTH( iVcSearchString );
> LET lSiStringLengthReplace = LENGTH( iVcReplaceString );
> LET oVcString = "";
> LET lSiIndex = 1;
>
> WHILE ( lSiIndex <= lSiStringLength )
>
> IF ( SUBSTR( iVcString, lSiIndex, lSiStringLengthSearch ) =
> iVcSearchString ) THEN
>
> LET oVcString = oVcString || iVcReplaceString;
>
> IF ( lSiStringLengthReplace = 0 ) THEN
>
> LET lSiIndex = lSiIndex + 1;
>
> ELSE
>
> LET lSiIndex = lSiIndex + lSiStringLengthSearch;
>
> END IF;
>
> ELSE
>
> LET oVcString = oVcString || SUBSTR( iVcString, lSiIndex, 1
> );
>
> LET lSiIndex = lSiIndex + 1;
>
> END IF;
>
> END WHILE;
>
> RETURN oVcString;
>
> END FUNCTION;
>
> ----------------------------------------------------------------------------
> -
>
> I then used the following to update:
>
> update hist_podesc set desc = searchReplace( desc, '> ', '#' );
>
> Yes, I hit enter for the newline instead of putting in a special
> character.
>
> Lennie
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
> STEPHEN SCOTT
> Sent: Wednesday, April 11, 2007 3:06 PM
> To: ids@iiug.org
> Subject: Re: Sql Replacing a char [8840]
>
> Here is the table structure:
> create table hist_podesc (
> po_num INT not null,
> orderedby_yard SMALLINT,
> sku INT not null,
> seq SMALLINT not null,
> desc VARCHAR(55),
> sequence SMALLINT,
> price DECIMAL(9,3)
> );>
> EXAMPLE DATA
> po_num = 90010564
> orderedby_yard = 3011
> sku = 2712406
> seq = 7
> desc = "Taupe.
> Packaging instructions:
> Pack 4 rockers in a"
> sequence = 11464
> price = 1.12
>
> I would need to replace the desc field to replace the newline with a "#"
> so
> it
> would look like the following:
>
> "Taupe.#Packaging instructions:#Pack 4 rockers in a"
>
> I tried
>
> update hist_podesc set desc = replace(desc,"\\\\^M" ,"#") where po_num => 90010564
>
> That didn't work. Is there any way to pass the ASCII value into that
> function.
>
> Stephen
>
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
On 4/10/07, STEPHEN SCOTT <dba.lumber@gmail.com> wrote: > We have a table with 156 million rows. On approx 100,000 records we need to > replace a NewLine Character in one field {type char(50)} with some other > character. Is there some function that can do that within an update statement. > > I found the replace function {replace (string,x,y)} but I have not found a way > to pass into it the new-line character. A while ago (2006, possibly 2005), I posted a pair of stored procedures for this sort of job. The relevant function is ASCII(10) which generates a ^J (LF). The code should be on the IIUG web site. The other function is CHR('x') which returns the integer corresponding to the (first) character of the argument. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0226 -- http://dbi.perl.org/ NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.