Oracle PL SQL remove space characters from a string

Question

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

User · Accepted Answer

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

User · Answer

select regexp replace  This is a test        chr 9       foo        space          from dual   REGEXP REPLACE -------------- Thisisatestfoo

User · Answer

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 gt  ed Wrote file afiedt buf    1  select          2         regexp replace   foo      chr 9       space                3               4    from dual SQL gt           -----  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

User · Answer

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

User · Answer

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   gt  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

User · Answer

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

[regex] Oracle PL/SQL : remove "space characters" from a string

Examples related to regex

Examples related to oracle

Examples related to plsql