CODEWITHSUNDEEP
regexpostgresql
AURIGADL
AURIGADL

Reputation: 2032

how I can write this regular expression for postgresql?

how I can write this regular expression?

origin => 'fo3o321bar423ba 234 z' result => "fo 3 o 321 bar 423 ba 234 z"

select regexp_replace(trim(regexp_replace(trim(regexp_replace('fo3o321bar423ba 234 z', E'([a-z]*[0-9]*)', E'\\1 ', 'g')), E'([0-9]*[a-z]*)', E'\\1 ', 'g')),'[\s.]+', ' ', 'g')

Upvotes: 2

Views: 310

Answers (2)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626927

You can achieve what you want with the following regexp_replace:

SELECT regexp_replace('fo3o321bar423ba 234 z', '([^0-9]|^)([0-9]+)(?=[^0-9]|$)', '\1 \2 ', 'g');

See demo

Result: fo 3 o 321 bar 423 ba 234 z

Regex matches:

  • (^|[^0-9]) - start of string or a non-digit and stores in Group 1
  • ([0-9]+) - 1 or more digits
  • (?=[^0-9]|$) - a lookahead check if there is a non-digit or end of string right after.

The replacement string - '\1 \2 ' - back references the captured groups 1 and 2 defined in the pattern with (...).

'g' means we want to replace all occurrences, not just the first.

Upvotes: 1

Neil McGuigan
Neil McGuigan

Reputation: 48256

You'll probably need a function to do it. PL/Perl might be best as Perl supports regex look-behind, but here is some working JavaScript, as that's all I have:

create extension if not exists plv8;

create or replace function delim(s text) returns text language plv8 as $$
    var match;
    var result = [];
    while (Boolean(match = s.match(/([A-Z]+|[A-Z]?[a-z]+|[0-9]+|([^a-zA-Z0-9])+)$/))) {
        if (!match[2]) {
            result.unshift(match[1]); /* don't return non alphanumeric tokens */
        }
        s = s.substring(0, s.length - match[1].length);
    }
    return result.join(" ");
$$;


select delim('fo3o321bar423ba 234 z'); --result fo 3 o 321 bar 423 ba 234 z

Upvotes: 0

Related Questions