sql - How do I use an ORACLE REGEX function to remove all leading and trailing line break characters and spaces?

Question

How do I use an ORACLE REGEX function to remove all leading and trailing line break characters and spaces?

For example, assume I have the following string where refers to actual invisible carriage return line feed characters. Here's the input:

"     
   
   
   SELECT * 
   FROM
   TABLE
              
             "

And here's the desire output:

"SELECT *
   FROM 
   TABLE"

question from:https://stackoverflow.com/questions/65902484/how-do-i-use-an-oracle-regex-function-to-remove-all-leading-and-trailing-line-br

Answer 1

This would do it if regex_replace() is a requirement:

select regexp_replace('     
   
   
   SELECT * 
   FROM
   TABLE
              
             ', '^s*|s*$', '') as hello
from dual

See https://www.techonthenet.com/oracle/functions/regexp_replace.php for documentation.