Search code examples
sqloracle-databaseregexp-replace

REGEXP to use for String formatting to group characters and numbers separated with spaces

Hello I am trying to format a given string so that it groups numbers and characters and puts spaces ' ' in between. For example the given string: 01ABC23fg98 should give the output: 01 ABC 23 fg 98

Any suggestions?

I tried REGEXP_REPLACE(input_String , ' ', '') which does the opposite (it removes spaces in a given string)

Solution

  • This can work if we use REGEXP_REPLACE() with a capture group:

    SELECT input_String,
       TRIM(REGEXP_REPLACE(input_String, '([A-Za-z]+)', ' \1 ')) AS output_String
FROM yourTable;

    Demo

    This regex replacement replaces all letter word substrings with the same substring surrounded by spaces on both sides. Appreciate that this effectively separates letter word substrings from number substrings. The outer call to TRIM() is necessary because should a letter word occur at the start or end of the input string, the replacement would result in a leading or trailing space.