In my Oracle 10g database I would like to remove "space characters" (spaces, tabs, carriage returns...) from the values of a table field.
Is TRANSLATE() the way to go ? For example something like:
MY_VALUE := TRANSLATE(MY_VALUE,
CHR(9) || CHR(10) || CHR(11) || CHR(12) || CHR(13) || ' ', '');
Or is there any better alternative (something like [:space:] in PHP PCRE) ?
Thanks for any piece of advice.
I'd go for regexp_replace, although I'm not 100% sure this is usable in PL/SQL
my_value := regexp_replace(my_value, '[[:space:]]*','');
REGEXP_REPLACE (download.oracle.com/docs/cd/B19306_01/server.102/b14200/…). Seems that's what I need since it claims to be "POSIX" compatible so [[:space:]] should be recognized.Shorter version of:
REGEXP_REPLACE( my_value, '[[:space:]]', '' )
Would be:
REGEXP_REPLACE( my_value, '\s')
Neither of the above statements will remove "null" characters.
To remove "nulls" encase the statement with a replace
Like so:
REPLACE(REGEXP_REPLACE( my_value, '\s'), CHR(0))
Since you're comfortable with regular expressions, you probably want to use the REGEXP_REPLACE function. If you want to eliminate anything that matches the [:space:] POSIX class
REGEXP_REPLACE( my_value, '[[:space:]]', '' )
SQL> ed
Wrote file afiedt.buf
1 select '|' ||
2 regexp_replace( 'foo ' || chr(9), '[[:space:]]', '' ) ||
3 '|'
4* from dual
SQL> /
'|'||
-----
|foo|
If you want to leave one space in place for every set of continuous space characters, just add the + to the regular expression and use a space as the replacement character.
with x as (
select 'abc 123 234 5' str
from dual
)
select regexp_replace( str, '[[:space:]]+', ' ' )
from x
select regexp_replace('This is a test ' || chr(9) || ' foo ', '[[:space:]]', '') from dual;
REGEXP_REPLACE
--------------
Thisisatestfoo
To replace one or more white space characters by a single blank you should use {2,} instead of *, otherwise you would insert a blank between all non-blank characters.
REGEXP_REPLACE( my_value, '[[:space:]]{2,}', ' ' )
To remove any whitespaces you could use:
myValue := replace(replace(replace(replace(replace(replace(myValue, chr(32)), chr(9)), chr(10)), chr(11)), chr(12)), chr(13));
Example: remove all whitespaces in a table:
update myTable t
set t.myValue = replace(replace(replace(replace(replace(replace(t.myValue, chr(32)), chr(9)), chr(10)), chr(11)), chr(12)), chr(13))
where
length(t.myValue) > length(replace(replace(replace(replace(replace(replace(t.myValue, chr(32)), chr(9)), chr(10)), chr(11)), chr(12)), chr(13)));
or
update myTable t
set t.myValue = replace(replace(replace(replace(replace(replace(t.myValue, chr(32)), chr(9)), chr(10)), chr(11)), chr(12)), chr(13))
where
t.myValue like '% %'
TRANSLATEwon't work because you have NULL as the third parameter. You could useTRANSLATE(my_value,'A'||CHR(9)||CHR(10)||CHR(11)||CHR(12)||CHR(13)||'','A')