pajama

마농님, 정상규님 댓글 감사합니다.

마농님이 올려주신 오라클에서의 쿼리는 적용이 되는데.

인포믹스에서는 12버전이 아니라 적용이 안되네요.

regex_replace 를 사용하지 않고 인포믹스에서도 가능할까요??

오라클 기본적인 문자함수들로만 구성해 봤습니다.
인포믹스에 맞게 고쳐 보세요.

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
;