Formatting with Carriage Returns -Oracle

[10PoundBrown]

Hello all and thanks for reading my post.

I have a table in my oracle db that is tracking user comments/notes for each record that is entered. The problem is, the notes are chock full of tabs, carriage returns, white space, commas and other punctuation. When I run a select statement on this column, the formatting is screwed!! Example: SELECT BOM_ID, NOTES FROM BOMS;
1788 9/26/03 Status: Sent to Logistics
10/9/03 Status: LR assigned. 02/02/04 STATUS: RACKS FOR PO025400

1789 9/26/03 Status: Sent to Logistics
10/8/03 Status: LR assigned. 02/02/04 STATUS: RACKS FOR PO025244.

1790 9/26/03 Status: Sent to Logistics
10/6/03 Status: LR assigned. 02/02/04 STATUS: RACKS FOR PO025258


Is there a good way to eliminate carriage returns and white space in my output?? I could do this by hand but I have over 4000 records and my day would suck!!

Thanks in advance for any help.
10PoundBrown

 

[goranm]

To eliminate carriage returns I think you can try something like:

UPDATE
BOMS
SET
NOTES = REPLACE(NOTES, CHR(13),CHR(32))
WHERE
NOTES LIKE '%CHR(13)%'

/Goran