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

User · Accepted Answer

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 · Answer

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

User · Answer

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 · Answer

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

User · Answer

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 · Answer

This how I would implement it         REGEXP REPLACE text       space        space

User · Answer

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 · Answer

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 · Answer

TRIM BOTH chr 13   chr 10       FROM str

User · Answer

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    No spaces or ctrl chars at the beginning of the string No spaces or ctrl chars at the end of the string 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

User · Answer

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

User · Answer

Instead of using regexp replace multiple time use   s  as given below   SELECT regexp replace  TEXT     s       FROM dual

User · Answer

Below code can be used to Remove New Line and Table Space in text column  Select replace replace TEXT char 10      char 13

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to trim