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

Question

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.

Smitt

The "trim" function doesn't just remove a single character. It removes all whitespace. – Matchwood

Answer 1

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.

Microspore

This is wrong on two levels. Firstly, whenever you pass null (e.g. '') as the 3rd parameter to TRANSLATE, it will always return null. Secondly, TRANSLATE acts on the entire string, not just the beginning and ending of a string (as requested by OP). Refer: docs.oracle.com/cd/E11882_01/server.112/e10592/… – Embarrass

Jeffrey is -very- right in saying that this is broken, unless you replace '' with ' ' (whitespace). Additionally, translate allows to specify multiple characters to be replaced at once. This does the same thing: select trim(translate(' example ', chr(10) || chr(13) || chr(09), ' ')) as example from dual; Keep in mind, it still replaces these characters -everywhere- in the string. – Mccoy

Hmm, stackoverflow comments replace consecutive spaces with just one. There should be three spaces between the '' in the SQL statement above. – Mccoy

thanks. it was useful but it's better to use REPLACE instead of TRANSLATE. – Decomposition

Answer 2

This how I would implement it:

     REGEXP_REPLACE(text,'(^[[:space:]]*|[[:space:]]*$)')

Dogear

Not sure why this hasn't got more attention! It's the only right answer on the page. And concise too. – Comfortable

Correct but performance impacting. I wonder why Oracle does not allow such small functions without regex. – Songful

Caveat: Oracle's [:space:] character class doesn't seem to match non-breaking space. – Pictograph

or you can use the "\s" shorthand for whitespace characters: REGEXP_REPLACE(your_text, '(^\s+)|(\s+$)', '') – Chess

Answer 3

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.

Matchwood

Upvoted for REGEXP_REPLACE suggestion and the beautifully laid out example (there should be a special bonus for those on SO). But it doesn't do what the OP asked, which is only to remove at start and end of a multi-line string. I am going to take the liberty of editing to adjust the regexp accordingly. – Immortalize

BTW when testing, it's a lot easier to see what is going on if you set the 3rd parameter on regexp_replace to a '.' instead of null – Immortalize

Hi @Andrew - thanks for the upvote. However, I'll have to rollback your changes as your new version now means the example doesn't produce the stated output. The point of the example was to demonstrate the breadth of whitespace that was handled by the [[:space:]] and [[:ctrl:]] parameters. Perhaps you'd like to update the answer to include your modified expressions at the end of the answer after my simplified example. Also, don't agree with using '.' instead of null - the null makes it clear the your replacing with nothing. – Matchwood

This cuts of the last alpha character in my testing – Haricot

Answer 4

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;

Hamachi

Answer 5

TRANSLATE (column_name, 'd'||CHR(10)||CHR(13), 'd')

The 'd' is a dummy character, because translate does not work if the 3rd parameter is null.

Phlyctena

Answer 6

For what version of Oracle? 10g+ supports regexes - see this thread on the OTN Discussion forum for how to use REGEXP_REPLACE to change non-printable characters into ''.

Autecology

Page not found.... That's why "link only answers" are bad – Team

Answer 7

I know this is not a strict answer for this question, but I've been working in several scenarios where you need to transform text data following these rules:

1. No spaces or ctrl chars at the beginning of the string
2. No spaces or ctrl chars at the end of the string
3. Multiple ocurrencies of spaces or ctrl chars will be replaced to a single space

Code below follow the rules detailed above:

WITH test_view AS (
  SELECT CHR(9) || 'Q   qwer' || CHR(9) || CHR(10) ||
         CHR(13) || ' qwerqwer     qwerty  ' || CHR(9) || 
         CHR(10) || CHR(13) str
  FROM DUAL
) SELECT 
     str original
    ,TRIM(REGEXP_REPLACE(str, '([[:space:]]{2,}|[[:cntrl:]])', ' ')) fixed
  FROM test_view;


ORIGINAL               FIXED                 
---------------------- ----------------------
    Q   qwer           Q qwer qwerqwer qwerty

 qwerqwer     qwerty                                         

1 row selected.

Stereotype

Answer 8

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 :)

Beatabeaten

Answer 9

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

Cressy

Answer 10

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.

Goatskin

Answer 11

Below code can be used to Remove New Line and Table Space in text column

Select replace(replace(TEXT,char(10),''),char(13),'')

Subtile

Doesn't work. There may be chr(13) or chr(10) in the middle of the string as originally posted in the question. – Comfortable

Answer 12

Try the code below. It will work if you enter multiple lines in a single column.

create table  products (prod_id number , prod_desc varchar2(50));

insert into products values(1,'test first

test second

test third');

select replace(replace(prod_desc,chr(10),' '),chr(13),' ') from products  where prod_id=2; 

Output :test first test second test third

Soppy

Answer 13

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

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

Tupler

Answer 14

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);

Dwell

Answer 15

CREATE OR REPLACE FUNCTION TRIM_WHITESPACE(pSTRIN IN NVARCHAR2) RETURN NVARCHAR2
IS
    v_length NUMBER := COALESCE(LENGTH(pSTRIN), 0);
    v_start_index NUMBER := 1;
    v_end_index NUMBER := v_length;
BEGIN
    IF pSTRIN IS NULL THEN
        RETURN pSTRIN;
    END IF;

    -- Remove leading whitespace characters
    FOR i IN 1..v_length LOOP
        IF ASCII(SUBSTR(pSTRIN, i, 1)) NOT IN (9, 10, 13, 32) THEN
            v_start_index := i;
            EXIT;
        END IF;
    END LOOP;

    -- Remove trailing whitespace characters
    FOR i IN REVERSE v_start_index..v_length LOOP
        IF ASCII(SUBSTR(pSTRIN, i, 1)) NOT IN (9, 10, 13, 32) THEN
            v_end_index := i;
            EXIT;
        END IF;
    END LOOP;

    -- Return the trimmed string
    RETURN SUBSTR(pSTRIN, v_start_index, v_end_index - v_start_index + 1);
END;

Primarily

Answer 16

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

Jamajamaal

Good to give some code, but it should come with some explanation to point the changes and why it is the solution of the OP's question. – Kovacs

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Sultanate

ORA-30001: trim set should have only one character – Trixi