How to remove spaces between words and replace with single underscore using ‘REPLACE’,’INSTR’ OR ‘SUBSTR’ oracle function

oraclereplace

I want to replace spaces between words with a single underscore. I can't use the replace_regex Oracle function to do this, because it is not supported by the converter which we are using to convert sql statements so that they will be independent of platform such as SQL Server, PostgreSQL etc. .

Currently converter is providing support for 'substr' , 'replace', and 'instr' oracle functions. how can i use these three functions to get below output.
Example:

"my name is            xyz" =>  "my_name_is_xyz"
"test    sdf"               =>  "test_sdf"

Best Answer

Something like this might work for you, at least if replace does the same on all your platforms as in SQL Server.

  1. Replace all spaces with space+underscore
  2. Replace underscore+space with an empty string
  3. Replace space with an empty string
replace(replace(replace('my name is            xyz', ' ', '_ '), ' _', ''), ' ', '');
Related Question