| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> FW: Diff quest about space in a text string
Larry,
I just wanted to say that your solution worked like a charm. I got the list of non-date, non-numeric columns and did the UPDATE, SET, REPLACE on each of these columns in the corrupted table. I created a test table as select * from the corrupted one and tested away. Every last occurrance of the erroneous chars was converted to a space; we're ready to run in production! :-)
Thanks again,
Tamara Swilley
AGENCY.COM
Avon, Colorado 81620
tswilley_at_agency.com
-----Original Message-----
From: Larry G. Elkins [mailto:elkinsl_at_flash.net]
Sent: Wednesday, August 23, 2000 6:51 PM
To: ORACLE-L_at_fatcity.com
Cc: tswilley_at_agency.com
Subject: RE: Diff quest about space in a text string
Tamara,
Look into using the CHR function. For example, instead of looking for a space as I did, you could look for CHR(160) -- e.g. INSTR(foo,CHR(160)). The CHR function is a means for specifying not so easily enterable data. Also look at the DUMP function. It will return the internal representation of the data. So, in your case where you said you could only see it in spooled output using vi, you could use the DUMP function on the column in a SELECT statement to see what is "really" there. Other functions you might be interested in are ASCII, TRANSLATE, and REPLACE.
If you want to simply replace all occurrences of the ASCII 160 in the column with a space, as it sounds like you want to do, look into using the REPLACE function:
update foo set foo = replace(foo,chr(160),' ')
You could restrict with a WHERE clause as necessary. For reference purposes, and I know it makes this a long response, here is an example scenario:
SQL> create table foo (foo varchar2(50));
Table created.
SQL> insert into foo values ('1234'||CHR(160)||'ELM STREET'||CHR(160)||'LAS VEGAS'); 1 row created.
SQL> ed
Wrote file afiedt.buf
1* insert into foo values ('3456'||CHR(160)||'ELM STREET
PLACE'||CHR(160)||'MEMPHIS');
SQL> /
1 row created.
SQL> select foo, dump(foo)
2 from foo;
FOO
3456 ELM STREET PLACE MEMPHIS
Typ=1 Len=29:
51,52,53,54,160,69,76,77,32,83,84,82,69,69,84,32,80,76,65,67,69,16
0,77,69,77,80,72,73,83
SQL> l
1 update foo
2* set foo = replace(foo,chr(160),' ')
SQL> /
2 rows updated.
SQL> select foo, dump(foo) from foo;
FOO
3456 ELM STREET PLACE MEMPHIS
Typ=1 Len=29:
51,52,53,54,32,69,76,77,32,83,84,82,69,69,84,32,80,76,65,67,69,32,
77,69,77,80,72,73,83
Notice that the ASCII 160 has been replaced with an ASCII 32, a space.
I apologize to you and the list for this being sooo long; but, I know how much examples and "seeing" something helps me. I hope this helps you out.
Regards,
L. Elkins
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Tamara
Swilley
Sent: Tuesday, August 22, 2000 4:10 PM
To: Multiple recipients of list ORACLE-L
Subject: Diff quest about space in a text string
Larry,
Will these scripts work on for special characters as well? I need to make a change to some corrupt data that has embedded '\240' (octal value, '160' ascii value) instead of space to separate the words. The data looks like (for example): 1234\240Sunset\240Drive..... and needs to look like 1234 Sunset Drive. Will your scripts find these finds of characters that are "hidden" (you can only see that something's wrong when you vi the listing from a spool of the data, not when you just select in sql*plus).
Scripting is one of my biggest weaknesses, so any help would be appreciated. I need to put this into a pl/sql script. I can build the declare section, open and close a cursor, code an exception handling message, and I know how to compile and test... BUT what I *don't* know is to do is code the part that would take the strings and parse through them, and find and replace these hidden octal/ascii chars with spaces. Received on Tue Aug 29 2000 - 16:45:22 CDT
 |
 |