Search code examples
sqloracle-databasetrim

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

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.

Solution

  • 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.