|
|
| Re: HOW TO REMOVE CONTROL CHARACTERS IN ORACLE TABLE FIELD [message #7171 is a reply to message #7164] |
Mon, 26 May 2003 04:05   |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Use ASCII/CHR to filter out the undesired characters.
Horizontal tab = ASCII 9
NewLine = ASCII 10
Vertical Tab = ASCII 11
NewPage = ASCII 12
Carriage Return = ASCII 13
Delete = ASCII 95
Method 1: You have a limited set of special characters in the text (let's say only newline and newpage are present):UPDATE your_table
SET your_column = REPLACE( REPLACE( your_column, chr(12),''),chr(10),'')
/
Method 2: You have a lot of special characters to omit and you don't like nesting them in replace statements.
Write a function which scans through a given text and which strips all characters that aren't in the ASCII range of desired characters. It should be something like: FUNCTION fun$strip(p_text in VARCHAR2)
RETURN VARCHAR2
IS
v_return VARCHAR2(2000) := NULL;
BEGIN
For i In length(p_text)
Loop
IF ASCII(substr(p_text,i,1)) in (your_range_of_valid_chars) Then
v_return := v_return||substr(p_text,i,1);
END IF;
End Loop;
RETURN v_return;
END;<PRE>In SQL you could do an update using the function:<PRE>UPDATE your_table
SET your_column = fun$strip(your_column);
The code in the function is written on the fly, so check ( and improve ) before using it. It's just to give you an idea.
HTH,
MHE
|
|
|
|
| Finding those with control characters [message #11486 is a reply to message #7171] |
Mon, 29 March 2004 10:39 |
Matthew Iskra
Messages: 16
Registered: April 2002
Location: Sacramento CA USA
|
Junior Member |
|
|
instead of updating everything, use a select to see which columns offend and then modify your update to only modify the qualifiying rows.
Exempli Gratia:
SELECT your_column
FROM your_table
WHERE your_column LIKE '%'||CHR(10)||'%';
That should assist those, like, that have huge tables with only a few offenders.
--Matthew
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
