How to Remove Carriage Return from a Column

Troy Coleman

How to Remove Carriage Return from a Column
You may have a null value. Give this a try:

select a.location_id, replace(value(a.hours,x'4040'), X'0D25',X'4040')
from s05dtdb.veap_location a


Troy Coleman, Support Engineer
IBM Certified Database Administrator - DB2 9 for z/OS and LUW

SoftBase Systems, Inc.
847-776-0618
828-670-9900 ext. 334
[login to unmask email]

Compliance Challenged with Test Data Privacy? White Papers and More at http://www.softbase.com/

The information contained in this message may be CONFIDENTIAL and is for the intended addressee only. Any unauthorized use, dissemination of the information, or copying of this message is prohibited. If you are not the intended addressee, please notify the sender immediately and delete this message.



Robert Badger wrote:
>
> 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 >
>


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * 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