How to Remove Carriage Return from a Column

Rob Badger

How to Remove Carriage Return from a Column
I have a column (HOURS) created as varchar(256) and it has multiple X'0D'
carriage return). I am trying to remove the carriage return values from
this column with the replace command.

Here is my SQL
select a.location_id, replace(a.hours, X'0D25',X'4040')
from s05dtdb.veap_location a

I am getting the following error:

Error: SQL0171N The data type, length or value of
argument "1" of routine "REPLACE" is incorrect.
SQLSTATE=42815 (State:42815, Native Code: FFFFFF55)

Thanks in advance for any assistance.

Robert Badger
Database Administrator
Aurora Health Care Inc.
3031 West Montana Street
Milwaukee, WI 53215
(414) 389-3589
[login to unmask email]

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

SUBSCRIBE DB2-L Muthuraj

Re: How to Remove Carriage Return from a Column
(in response to Rob Badger)
Hi Robert,

Please try Translate function in place of Replace. Hope it might work.

Regards,
Muthu

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Philip Sevetson

Re: How to Remove Carriage Return from a Column
(in response to SUBSCRIBE DB2-L Muthuraj)
Bob,

I can't reproduce your error. Can you reproduce these results below
with SPUFI or some other SQL processor?



********************************* Top of Data *********************

---------+---------+---------+---------+---------+---------+-------

SELECT REPLACE(HOURS,X'0D25',X'4040')

FROM

(

SELECT CAST('XXXX' || X'0D25' || 'XXXX' AS VARCHAR(256)) AS HOURS

FROM SYSIBM.SYSDUMMY1

) AS T1

;

---------+---------+---------+---------+---------+---------+-------



---------+---------+---------+---------+---------+---------+-------

XXXX XXXX

DSNE610I NUMBER OF ROWS DISPLAYED IS 1



--Phil

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Robert Badger
Sent: Tuesday, December 30, 2008 10:57 AM
To: [login to unmask email]
Subject: [DB2-L] How to Remove Carriage Return from a Column




I have a column (HOURS) created as varchar(256) and it has multiple
X'0D' carriage return). I am trying to remove the carriage return values
from this column with the replace command.

Here is my SQL
select a.location_id, replace(a.hours, X'0D25',X'4040')
from s05dtdb.veap_location a

I am getting the following error:

Error: SQL0171N The data type, length or value of
argument "1" of routine "REPLACE" is incorrect.
SQLSTATE=42815 (State:42815, Native Code: FFFFFF55)

Thanks in advance for any assistance.

Robert Badger
Database Administrator
Aurora Health Care Inc.
3031 West Montana Street
Milwaukee, WI 53215
(414) 389-3589
[login to unmask email]

________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >



=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========



______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Mike Bell

Re: How to Remove Carriage Return from a Column
(in response to Philip Sevetson)
one of the restrictions of REPLACE and TRANSLATE is they won't take a null
string. Does one of your rows have null value for that column?

Mike
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Sevetson, Phil
Sent: Tuesday, December 30, 2008 12:44 PM
To: [login to unmask email]
Subject: Re: [DB2-L] How to Remove Carriage Return from a Column

Bob,

I can't reproduce your error. Can you reproduce these results below with
SPUFI or some other SQL processor?



********************************* Top of Data *********************

---------+---------+---------+---------+---------+---------+-------

SELECT REPLACE(HOURS,X'0D25',X'4040')

FROM

(

SELECT CAST('XXXX' || X'0D25' || 'XXXX' AS VARCHAR(256)) AS HOURS

FROM SYSIBM.SYSDUMMY1

) AS T1

;

---------+---------+---------+---------+---------+---------+-------



---------+---------+---------+---------+---------+---------+-------

XXXX XXXX

DSNE610I NUMBER OF ROWS DISPLAYED IS 1



--Phil

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Robert Badger
Sent: Tuesday, December 30, 2008 10:57 AM
To: [login to unmask email]
Subject: [DB2-L] How to Remove Carriage Return from a Column




I have a column (HOURS) created as varchar(256) and it has multiple X'0D'
carriage return). I am trying to remove the carriage return values from this
column with the replace command.

Here is my SQL
select a.location_id, replace(a.hours, X'0D25',X'4040')
from s05dtdb.veap_location a

I am getting the following error:

Error: SQL0171N The data type, length or value of argument
"1" of routine "REPLACE" is incorrect.
SQLSTATE=42815 (State:42815, Native Code: FFFFFF55)

Thanks in advance for any assistance.

Robert Badger
Database Administrator
Aurora Health Care Inc.
3031 West Montana Street
Milwaukee, WI 53215
(414) 389-3589
[login to unmask email]

________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org > was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >

________________________________

=========
Confidentiality Notice: This e-mail communication, and any attachments,
contains confidential and privileged information for the exclusive use of
the recipient(s) named above. If you are not an intended recipient, or the
employee or agent responsible to deliver it to an intended recipient, you
are hereby notified that you have received this communication in error and
that any review, disclosure, dissemination, distribution or copying of it or
its contents is prohibited. If you have received this communication in
error, please notify me immediately by replying to this message and delete
this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author
and do not necessarily represent the opinions of the agency or the City.
=========




________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org > was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html