Tags: carriage, characters, column, control, database, db2, guys, ideas, mysql, oracle, returns, special, sql, table

REMOVE SPECIAL CHARACTERS in a column data

On Database » DB2

2,131 words with 4 Comments; publish: Sun, 02 Dec 2007 22:09:00 GMT; (25094.73, « »)

Guys,

Anyone has ideas about how to remove control characters such as Carriage returns from a data column in a DB2 database table.

--RAHool

All Comments

Leave a comment...

  • 4 Comments
    • Hi,

      Can we try the following options

      1. Export the table data to a file using the export utility (DEL format)

      2. Modify the file to remove the control characters

      3. Load with replace option to table with an empty file

      3. Import the modified file to the table.

      Thanks

      Sateesh

      #1; Tue, 11 Dec 2007 17:45:00 GMT
    • === Original Words ===

      msateeshkumar

      Hi,

      Can we try the following options

      1. Export the table data to a file using the export utility (DEL format)

      2. Modify the file to remove the control characters

      3. Load with replace option to table with an empty file

      3. Import the modified file to the table.

      Thanks

      Sateesh

      Exporting to a file and loading it back into the database is a issue, as there are multiple control characters in the data. Also, the REPLACE function is not much of help as the chr() to get the Control characters seems to be a issue. Its not working properly or i might not be using the right chr() function.

      I am using REPLACE(column1, chr(10), '').

      But his is not helping even chr(13) is not helping ...

      thanks for the help mate..

      --RAHool

      #2; Tue, 11 Dec 2007 17:46:00 GMT
    • Hi,

      Can you send me the list of control characters which exist in the database.

      Thanks

      Sateesh

      === Original Words ===

      gemini95117

      Exporting to a file and loading it back into the database is a issue, as there are multiple control characters in the data. Also, the REPLACE function is not much of help as the chr() to get the Control characters seems to be a issue. Its not working properly or i might not be using the right chr() function.

      I am using REPLACE(column1, chr(10), '').

      But his is not helping even chr(13) is not helping ...

      thanks for the help mate..

      --RAHool

      #3; Tue, 11 Dec 2007 17:47:00 GMT
    • Thanks,

      I am attaching a txt file for your reference. It has the table details and the data (3 rows) which is split across multiple lines.

      --RAHool

      #4; Tue, 11 Dec 2007 17:48:00 GMT