Control Characters in column

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • andy
    #1

    Control Characters in column

    Greetings all.
    Problem: Have control characters mostly ^B which are loaded into a
    table in our database forming part or a narative field. In TOAD these
    appear as a thick bar - kind of like a pipe character '|' but thicker.
    We need to get rid of these as they are causing problems with our
    information exchange software. Does anybody have any idea how to do a
    search and replace for such characters in an oracle 9i database? The
    table has over 60M record so I dont facy checking it manually as I
    recon Ill be 84 years old by the time im finished!
    Thanks.
    Tags: None
    • Mark C. Stock
      #2
      Re: Control Characters in column


      "andy" <andyclifton@ya hoo.co.ukwrote in message
      news:475aa3f5.0 402260528.44605 7f8@posting.goo gle.com...
      | Greetings all.
      | Problem: Have control characters mostly ^B which are loaded into a
      | table in our database forming part or a narative field. In TOAD these
      | appear as a thick bar - kind of like a pipe character '|' but thicker.
      | We need to get rid of these as they are causing problems with our
      | information exchange software. Does anybody have any idea how to do a
      | search and replace for such characters in an oracle 9i database? The
      | table has over 60M record so I dont facy checking it manually as I
      | recon Ill be 84 years old by the time im finished!
      | Thanks.

      use the replace and the chr functions -- if i got the chr code right, this
      should work, as long as you've got a sufficiently large rollback segment:

      update the60mtable
      set thebadcolumn = replace(thebadc olumn,chr(2),nu ll)
      where thebadcolumn like '%'||chr(2)||'% '

      ;-{ mcs


        Comment

        • andy
          #3
          Re: Control Characters in column

          "Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message news:<NuOdnSnBD _OvaqDd4p2dnA@c omcast.com>...
          "andy" <andyclifton@ya hoo.co.ukwrote in message
          news:475aa3f5.0 402260528.44605 7f8@posting.goo gle.com...
          | Greetings all.
          | Problem: Have control characters mostly ^B which are loaded into a
          | table in our database forming part or a narative field. In TOAD these
          | appear as a thick bar - kind of like a pipe character '|' but thicker.
          | We need to get rid of these as they are causing problems with our
          | information exchange software. Does anybody have any idea how to do a
          | search and replace for such characters in an oracle 9i database? The
          | table has over 60M record so I dont facy checking it manually as I
          | recon Ill be 84 years old by the time im finished!
          | Thanks.
          >
          use the replace and the chr functions -- if i got the chr code right, this
          should work, as long as you've got a sufficiently large rollback segment:
          >
          update the60mtable
          set thebadcolumn = replace(thebadc olumn,chr(2),nu ll)
          where thebadcolumn like '%'||chr(2)||'% '
          >
          ;-{ mcs
          Thanks Mark,
          Never thought to cat the %'s to the chr function...Ill away and upset the DBAs now!

            Comment

            Previous template Next
            Working...