Regular expression pattern change function REGEX_REPLACE

Hi. Informix has a MATCHES keyword that you can search using regular expressions in conditional terms.

Oracle's REGEXP_REPLACE function, which changes the pattern retrieved by regular expressions, is supported in version 12.

I found out about the pattern change in the Database Sarang Net and tested it in the Informix environment.

Below are the posts you have posted.

The column data

'문자001'

'문자열01'

'01문자001'

'121문자열01'

'문자002'

'문자003'

In this way ... I have a column that contains multiple columns.

Only the digits after 001 should be numbered with 0001

So the result is

'문자0001'

문자열0001'

01문자0001'

121문자열0001'

'문자0002'

'문자0003'

This number should be represented in 4 digits in common.

I think we should separate the letters and digits (the serial numbers on the back) and then pad the back numbers to 0000.

Is this possible with Oracle and Informix?

Source: Database LoveNet (http://database.sarang.net/?criteria=oracle&subcrit=qna&inc=read&aid=41526)

Thanks to Manon, who is a stronghold of the Database Sarang Net, he posted the following query in the reply, and it was easy to test it.

WITH t AS

(

SELECT 'character 001' v FROM dual

UNION ALL SELECT 'string 01' FROM dual

UNION ALL SELECT '01 Character 001 'FROM dual

UNION ALL SELECT '121 String 01' FROM dual

UNION ALL SELECT 'character 002' FROM dual

UNION ALL SELECT 'character 003' FROM dual

)

- Oracle regular expressions -

SELECT v

     , REGEXP_REPLACE (REGEXP_REPLACE (v

       , '([0-9] +) $', '000 \ 1'

       , '0 + ([0-9] {4}) $', '\ 1'

       AS x

  FROM t

;

I've done the following in dbaccess. I cast it to varchar (20) to make it look better.

- Temp table creation and data entry -

create temp table t (v varchar(20));

insert into t values ('문자001');

insert into t values ('문자열01');

insert into t values ('01문자001');

insert into t values ('121문자열01');

insert into t values ('문자002');

insert into t values ('문자003');

- Result of using REGEX_REPLACE function -

SELECT v::varchar(20)

       AS v

       , REGEX_REPLACE(REGEX_REPLACE(v

       , '([0-9]+)$', '000\1')

       , '0+([0-9]{4})$', '\1')::varchar(20)

       AS x

  FROM t

;

v                    x

문자001              문자0001

문자열01             문자열0001

01문자001            01문자0001

121문자열01          121문자열0001

문자002              문자0002

문자003              문자0003

6 row (s) retrieved.

REGEX_REPLACE is an extension of Informix that installs the function the first time it is called.

It is generated in the form of an external function. If it is executed without being installed, it seems to be installed automatically.

You can check the Informix message logs and database schema for relevant information.

$ onstat -m

...

15:39:53 Logical Log 420 Complete, timestamp: 0xe75412.

15:40:09 INFO (autoregexe 13) (EXECUTE FUNCTION sysbldprepare ('ifxregex. *', 'Create'))

$ dbschema -d kscdb -f all

...

create function "informix" .regex_replace (lvarchar, lvarchar, lvarchar, integer default null, integer default null, integer default null)

returns lvarchar

with (parallelizable, handlesnulls, not variant, percall_cost = 1000)

external name '$ INFORMIXDIR / extend / ifxregex.1.00 / ifxregex.bld (ifxregex_replace_char)' language c;

Please note that it may be useful to use.

References

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.dbext.doc/ids_dbxt_559.htm