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

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

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.

Replace spaces, tabs and carriage returns

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

How to handle string with only space in oracle sql?

I hope this could be Insight of your issue.

select 
    TO_NUMBER(trim(colA)),  
    TO_NUMBER(REGEXP_REPLACE(colA,'(^[[:space:]]*|[[:space:]]*$)')),
    regexp_instr(colA, '[0-9.]')    
from 
(
    select '   123' colA from dual
    union all
    select '   ' colA from dual
    union all
    select '.456' colA from dual
)

This is similar issue : Trim Whitespaces (New Line and Tab space) in a String in Oracle

Oracle regexp_replace - removing trailing spaces

You want to remove space from the end of the string:

regexp_replace(queue_name, '[[:space:]]+$', '')

(The '$' in the pattern marks the end.)

If this still doesn't work, then you are dealing with some strange invisible character that is not considered space. Use

regexp_replace(queue_name, '[^0-9A-Za-z]+$', '')

instead, which, as you already know, removes all characters except for letters and digits. The '$' restricts this to the end of the string.

Find space after nth characters and split into new row

You do not need (slow) regular expressions and can do it with simple (quicker) string functions.

If you want to replace spaces with newlines then:

WITH bounds ( str, end_pos ) AS (
  SELECT col_large_string,
         INSTR(col_large_string, ' ', 101)
  FROM   tab_large_string
UNION ALL
  SELECT SUBSTR(str, 1, end_pos - 1)
         || CHR(10)
         || SUBSTR(str, end_pos + 1),
         INSTR(str, ' ', end_pos + 101)
  FROM   bounds
  WHERE  end_pos > 0
)
SELECT str AS split_to_lines
FROM   bounds
WHERE  end_pos = 0;

and if you want to have each line in a new row then:

WITH bounds ( str, start_pos, end_pos ) AS (
  SELECT col_large_string,
         1,
         INSTR(col_large_string, ' ', 101)
  FROM   tab_large_string
UNION ALL
  SELECT str,
         end_pos + 1,
         INSTR(str, ' ', end_pos + 101)
  FROM   bounds
  WHERE  end_pos > 0
)
SELECT CASE end_pos
       WHEN 0
       THEN SUBSTR(str, start_pos)
       ELSE SUBSTR(str, start_pos, end_pos - start_pos)
       END AS split_to_rows
FROM   bounds;

If you do want to use regular expressions then:

SELECT REGEXP_REPLACE(
         col_large_string,
         '(.{100,}?) ',
         '\1' || CHR (10)
       ) AS split_to_lines
FROM   tab_large_string
WHERE  string_id = 1;

db<>fiddle here

---

Related Topics