Trim Whitespaces (New Line and Tab space) in a String in Oracle

user_90773 0
Viewed 195376

I need to trim New Line (Chr(13) and Chr(10) and Tab space from the beginning and end of a String) in an Oracle query. I learnt that there is no easy way to trim multiple characters in Oracle. "trim" function trims only single character. It would be a performance degradation if i call trim function recursivelly in a loop using a function. I heard regexp_replace can match the whitespaces and remove them. Can you guide of a reliable way to use regexp_replace to trim multiple tabspaces or new lines or combinations of them in beginning and end of a String. If there is any other way, Please guide me.

trim oracle sql
15 Answers

If you have Oracle 10g, REGEXP_REPLACE is pretty flexible.

Using the following string as a test:

chr(9) || 'Q   qwer' || chr(9) || chr(10) ||
chr(13) || 'qwerqwer     qwerty' || chr(9) || 
chr(10) || chr(13)

The [[:space:]] will remove all whitespace, and the ([[:cntrl:]])|(^\t) regexp will remove non-printing characters and tabs.

select
    tester,
    regexp_replace(tester, '(^[[:space:]]+)|([[:space:]]+$)',null)
            regexp_tester_1,
    regexp_replace(tester, '(^[[:cntrl:]^\t]+)|([[:cntrl:]^\t]+$)',null) 
            regexp_tester_2
from
    (
    select
        chr(9) || 'Q   qwer' || chr(9) || chr(10) ||
                chr(13) || 'qwerqwer     qwerty' || chr(9) || 
                chr(10) || chr(13) tester 
    from 
        dual
    )

Returning:

  • REGEXP_TESTER_1: "Qqwerqwerqwerqwerty"
  • REGEXP_TESTER_2: "Q qwerqwerqwer qwerty"

Hope this is of some use.

user_40030

How about the quick and dirty translate function?

This will remove all occurrences of each character in string1: 

SELECT translate(
           translate(
               translate(string1, CHR(10), '')
           , CHR(13), '')
       , CHR(09), '') as massaged
FROM BLAH;

Regexp_replace is an option, but you may see a performance hit depending on how complex your expression is.

user_1200520

You could use both LTRIM and RTRIM.

select rtrim(ltrim('abcdab','ab'),'ab') from dual;

If you want to trim CHR(13) only when it comes with a CHR(10) it gets more complicated. Firstly, translated the combined string to a single character. Then LTRIM/RTRIM that character, then replace the single character back to the combined string.

select replace(rtrim(ltrim(replace('abccccabcccaab','ab','#'),'#'),'#'),'#','ab') from dual;
user_257140
user_1351520

If at all anyone is looking to convert data in 1 variable that lies in 2 or 3 different lines like below

'Data1

Data2'

And you want to display data as 'Data1 Data2' then use below

select TRANSLATE ('Data1

Data2', ''||CHR(10), ' ') from dual;

it took me hrs to get the right output. Thanks to me I just saved you 1 or 2 hrs :)

user_129338200

In cases where the Oracle solution seems overly convoluted, I create a java class with static methods and then install it as a package in Oracle. This might not be as performant, but you will eventually find other cases (date conversion to milliseconds for example) where you will find the java fallback helpful.

user_2745220 
TRIM(BOTH chr(13)||chr(10)||' ' FROM str)
user_69305760

Instead of using regexp_replace multiple time use (\s) as given below;

SELECT regexp_replace('TEXT','(\s)','')
FROM dual;
user_76166750

Fowloing code remove newline from both side of string:

select ltrim(rtrim('asbda'||CHR(10)||CHR(13) ,''||CHR(10)||CHR(13)),''||CHR(10)||CHR(13))  from dual

but in most cases this one is just enought :

select rtrim('asbda'||CHR(10)||CHR(13) ,''||CHR(10)||CHR(13)))  from dual
user_42383230 
UPDATE My_Table
   SET Mycolumn1 =
           TRIM (
               TRANSLATE (Mycolumn1,
                          CHR (10) || CHR (11) || CHR (13),
                          '    '))
 WHERE (   INSTR (Mucolumn1, CHR (13)) > 0
        OR INSTR (Mucolumn1, CHR (10)) > 0
        OR INSTR (Mucolumn1, CHR (11)) > 0);
user_32067950
Related