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

Question

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"

• Learning_DBAdmin: Why Oracle and SQL-Server both are tagged? I guess its Oracle question.

Answer 1

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', ' ', '_ '), ' _', ''), ' ', '');

• miracle173: It won't work correctly for 'a_ b', but maybe this is ok for the OP. Otherwise I think substituting all underscore+blank by underscore+underscore+blank before one proceeds with yor steps may help.
• Mikael Eriksson: @miracle173 Sure, depends on what you want. It is unclear in your example if the already existing underscore should be treated as part of a word or as an already existing delimiter. Only OP knows. BTW, these kind of string manipulation things gets complicated fast if you choose to use a special character (underscore in this case) that may already exist in the string you are manipulating.