Replace spaces, tabs and carriage returns

问题:

I am working with SQL developer with Oracle 11g. I have a query that looks something along the lines of this;

SELECT [column], [column], [column],...... rs.notes
FROM [table], [table], [table]............ return_sku rs
WHERE [conditions] AND [conditions] AND [conditions]

In the return_sku column there are tabs, spaces, and newlines (I believe this is a carriage return?) I need to make all of these spaces, carriage returns and tabs disappear when I run my query.

I am fairly new to SQL, but the most popular search result I found is the REPLACE function. I have absolutely no idea how to use this, as I've tried this in many different ways with no result. I've tried the following;

SELECT [column], [column], [column],...... REPLACE(rs.notes, Char(10), '')
FROM [table], [table], [table]............ return_sku rs
WHERE [conditions] AND [conditions] AND [conditions]

This gives the error message:

ORA-00904: "RS"."NOTES": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 3 Column: 531

How do I use this function correctly?

回答1:

In Oracle if you just want to remove a character you can omit the third argument of the replace call and the function for character codes is chr(), not char().

So your line would be SELECT ... replace(rs.notes,chr(10)) ...

回答2:

This doesn't address the ORA-00904, which I suspect is a typo, but can't really tell without seeing the real code. But if you need to strip out several characters, you can do a nested replace:

with return_sku as (
    select 'This is '||chr(9)|| ' a split'||chr(10)||chr(13)||'line' as notes
    from dual
)
select replace(replace(replace(replace(rs.notes, chr(10)), chr(13)), chr(9)), ' ')
from return_sku rs;

REPLACE(REPLACE(
----------------
Thisisasplitline

... but a regexp_replace might be more manageable, if possibly a little more costly:

with return_sku as (
    select 'This is '||chr(9)|| ' a split'||chr(10)||chr(13)||'line' as notes
    from dual
)
select regexp_replace(rs.notes, '[ '||chr(10)||chr(13)||chr(9)||']')
from return_sku rs;

REGEXP_REPLACE(R
----------------
Thisisasplitline

I've included both line feed (chr(10)) and carriage return (chr(13)) in both; might be overkill. But if you do want to remove any whitespace, this gets even simpler:

select regexp_replace(rs.notes, '\s') ...

or the Posix version:

select regexp_replace(rs.notes, '[[:space:]]') ...

As @randcd pointed out, if the third argument to replace or regexp_replace is null (or an empty string, which is the same as null in Oracle) then you don't need to supply it.