How to concat substrings with regexp_matches (as analogy regexp_substr in teradata) in Postgres 9.4-CodePudding

I am having migration from teradata to greenplum and there is a string concatenation with regex_substr which is not existing at Postgres 9.4 version.

The part of the existing code is:

TRIM(COALESCE(REGEXP_SUBSTR('Office №9013/0713', '(?<=№)[0-9] ', 1,1,'i), '-1')(INT))||'_'||TRIM(COALESCE(REGEXP_SUBSTR('Office №9013/0713', '(?<=/)[0-9] ', 1,1,'i), '-1')(INT))

Results is follwing: 9013_713

I have tried using regexp_matches function as I checked lookbehind is not existing in 9.4.

Not sure if lookbehind '(?:№)([0-9] )' is correct for 9.4.

regexp_matches('Office №9013/0713', '(?:№)([0-9] )', 'gi') first part extraction after №.

regexp_matches('Office №9013/0713', '(?:\/)([0-9] )', 'gi') for second part extraction after /.

The problem is that the result above provides array and I cannot concat to single string. Is there any easy approach to achieve 9013_713 with simple structure?

CodePudding user response：

If you just want a single match returned (which is what regexp_substr() is typically doing), using substring(... from ...) is easier than regexp_matches() which returns multiple rows of arrays.

substring('Office №9013/0713' from '№([0-9] /[0-9] )')

returns 9013/0713

To concatenate the results as integers, you can use two substring() calls

concat(
  substring('Office №9013/0713' from '№([0-9] )/[0-9] ')::int,
  '_',
  substring('Office №9013/0713' from '№[0-9] /([0-9] )')::int
);

Note the different use of groups in the two substring() calls. Casting the result to an integer will drop leading zeros.

This will fail if the regex doesn't find any match and returns an empty string!