Removing Carriage Returns - SQL OS/390

[notadba]

Hi,

Using DB2 Command Centre (UDB 7.1) and need to get rid of carriage returns in my results. Carriage returns are held in 'long' fields - causing a problem with wrapping of text in the results.

I have tried replace and translate options but this is not stopping the wrapping - might be my SQL or identification of the right character to be looking for:

select course, replace(descr, X'13','') from course_tbl
where course = 'zzz'

Course Descr
ZZZ Certificate 1 Transport Course
and text continues merrily along on the enxt line

Translate appears to perform largely the same way for me - have also tried replazing x'10' and x'0D'...

Any ideas..

Thanks in advance

 

[blom0344]

How about:

select course, replace(descr,chr(13),'') from course_tbl
where course = 'zzz'

Ties Blom

 

[notadba]

Hi Ties,

Thanks for the response. I tried your version, but looks like this function is not working (for me)...

SQL0440N No function by the name "CHR " having compatible arguments was found in the function path. SQLSTATE=42884

May be that my access to the function catalog is not set correctly..

I am also looking at another approach - selecting a dummy character as the first column, then getting a vb script to clean up the file by joining lines where the dummy char is not in the first character position.. Have any experience doing that?

 

[blom0344]

CHR is a SYSFUN function. (instead of SYSIBM)

Ties Blom

 

[notadba]

When all else failed, I read the manual....

It looks like the ASCII and CHR conversion is UDB 8 not 7.1. Still waiting for the client roll-out here to take advantage of it...

Thanks for help - at least have the right script approach when we do get it ;-)

 

[3gm]

Note that X'13' is not a carriage return, it's X'0D'.

Glenn