How to change regular expression pattern using SUBSTR, LPAD

Hi. In the following article, I wrote about REGEX_REPLACE, the regular expression pattern change function of Informix.

However, since the REGEX_REPLACE function is only supported in Informix version 12, so the questioner asked again how to do it before version 12.



Thank you for your comment.
The query in Oracle that Manon's uploaded is applied.
I can not apply Informix 12 version.
Is it possible to use Informix without using regex_replace?




I have tested this query again with a query that was rewritten by the Database Sarang Net 'Manon', using basic string functions.




I have configured only basic Oracle character functions.
Please fix it for Informix.

WITH t AS
(
SELECT '문자001' v FROM dual
UNION ALL SELECT '문자열01'    FROM dual
UNION ALL SELECT '01문자001'   FROM dual
UNION ALL SELECT '121문자열01' FROM dual
UNION ALL SELECT '문자002'     FROM dual
UNION ALL SELECT '문자003'     FROM dual
UNION ALL SELECT '문자1'       FROM dual
UNION ALL SELECT '문자'        FROM dual
UNION ALL SELECT '문자11A03'   FROM dual
)
SELECT v
     , SUBSTR(v, 1, x-y) || LPAD(SUBSTR(v, x-y+1, 4), 4, '0') z
  FROM (SELECT v
             , x
             , CASE WHEN SUBSTR(v, x-0, 1) BETWEEN '0' AND '9' THEN 1
             + CASE WHEN SUBSTR(v, x-1, 1) BETWEEN '0' AND '9' THEN 1
             + CASE WHEN SUBSTR(v, x-2, 1) BETWEEN '0' AND '9' THEN 1
             + CASE WHEN SUBSTR(v, x-3, 1) BETWEEN '0' AND '9' THEN 1
               ELSE 0 END ELSE 0 END ELSE 0 END ELSE 0 END AS y
          FROM (SELECT v, LENGTH(v) x FROM t) a
        ) a
;



It worked well with Informix 11 version.

Note that the WITH clause can not be used in Informix.

It's really cool. I am grateful to Manon for giving me instructions on how to write the query.