Wildcard for White Space

[thermidor]

Hi All,

Is there a wildcard that can be used for all whitespace? I recently needed to do an rtrim on the value of a column where the value ended in carriage return, tab. Rather than hard code that, I'd like to use a wildcard.

TIA,
Sven

 

[SantaMufasa]

Sven,

Oracle's SQL does not have a "whitespace wildcard", per se. But if there is an embedded <carriage return> <tab> in your code, the following should easily condense both of them into a single blank, then trim it off if it appears at the end of the expression:

********************************************************************************
col a heading "Text with embedded <cr> <tab> characters" format a60
col b heading "Text with embedded <cr> <tab> characters stripped off" format a60
select '['||text||']' a from onecol;

Text with embedded <cr> <tab> characters
------------------------------------------------------------
[This text is all on one line, no <cr>, no <tab>.]
[This text has <cr> <tab> here: 
        followed by this text.]
[This text has trailing <cr> <tab> characters here: 
   ]
[This text is all on one line, no <cr>, no <tab>.]

4 rows selected.

select '['||trim(replace(text,chr(13)||chr(9),' '))||']'b from onecol;

Text with embedded <cr> <tab> characters stripped off
---------------------------------------------------------
[This text is all on one line, no <cr>, no <tab>.]
[This text has <cr> <tab> here:   followed by this text.]
[This text has trailing <cr> <tab> characters here:]
[This text is all on one line, no <cr>, no <tab>.]

4 rows selected.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:44 (02Sep04) UTC (aka "GMT" and "Zulu"), 11:44 (02Sep04) Mountain Time)