REPLACE STRING

The REPLACE STRING clause allows string replacements in the selected string columns of the SQL query.

In the following explanations:

replacement_pair stands for the following sequence “column_name|'literal' [BY column_name|'literal']”.

column_spec stands for "FOR|EXCEPT (column[,column,column...])"

A replacement_pair without column_spec can be specified along with one or more replacement_pair with column_spec. In such scenario, the replacement_pair without column_spec applies by default to all the columns not specified in a FOR option.

Syntax

REPLACE STRING (column_name | 'literal' [BY column_name | 'literal'] [ FOR|EXCEPT (column_name)] )

REPLACE STRING IN "filename"

Variables

column_name

The name of a column in the SELECT * statement.

'literal'

A string of characters or a character.

The first column_name/'literal' occurrence is the equivalent of the second argument search-string in the REPLACE() Db2® scalar function.

The second column_name/'literal' occurrence is the equivalent of the third argument replace-string in the REPLACE() Db2 scalar function. When this occurrence is omitted, the replacement string is an empty string.

"filename"

The name of a file containing strings replacements specifications.

Default

None.

Options

FOR (column_name): This option allows to specify one or multiple string columns on which the REPLACE STRING clause will be applied. The column_name is the equivalent of the first argument source-string in the REPLACE() Db2 scalar function.
EXCEPT (column_name): This option allows to specify one or multiple string columns on which the REPLACE STRING clause will not be applied.
IN "filename": The replacements to be applied to strings are specified in an external file.

Content of the file specified to the IN “filename” option

Each strings replacement into this file must be specified on its own and separate entry, and it cannot be split into several lines. There can be as many entries as necessary specified in the file. Each entry must be formatted according to the following models:

seek_value:replace_value;for:column1,...,columnn
seek_value;for:column1,...,columnn
seek_value:replace_value;except:column1,...,columnn
seek_value;except:column1,...,columnn
seek_value:replace_value;
seek_value;

The first and second models are for a string replacement related by the FOR option to a list of column names. The third and fourth models are for a string replacement related by the EXCEPT option to a list of column names. And the fifth and sixth models are for a string replacement not related to any list of column names.

The # character can be used to add a comment to the file.

An entry must contain one semicolon character (';'), in order to separate its two different components (string replacement, columns), even if it is not necessary to specify column names. The string replacement is mandatory, but the list of column names is an optional one. The string replacement must contain a seeked value, and can contain an optional replacement value. If it contains a replacement value, this value must be separated from the seeked value by a colon character (‘:’). The items into the lists of column names must be separated by comma characters (‘,’). When it is specified, a list of column names must follow the ‘for’ or ‘except’ keyword and a colon character (‘:’).

Using such an external file is an alternative of specifying a whole REPLACE STRING clause in a control file. As a result, for any entry into it, the meaning of its two components (string replacement, columns) is exactly the same one as the respective two components of an entire REPLACE STRING clause.

Rules

An error message will be displayed when the following rules are not followed:

FOR or EXCEPT options must only be used with a “SELECT *”.

There can be only one EXCEPT option in the REPLACE STRING clause.

When the EXCEPT and FOR options are used in a REPLACE STRING clause, a string column specified in a FOR option must be specified in the EXCEPT option.

There can be more than one FOR options in the REPLACE STRING clause but the string column names specified can appear only once.

Only one replacement_pair without column_spec is allowed.

A message will inform that the REPLACE STRING clause is ignored when a SELECT statement is processed through Db2.

When no options FOR or EXCEPT are specified in the REPLACE STRING clause, the string replacement will be made on all the string columns specified in the SQL query.

Examples

Using a “SELECT *” SQL query:

[i1010@lat179(:) ~]$ db2hpu -f sysin -i i1010
INZM031I Optim High Performance Unload for Db2 06.01.00.001(121203)
         64 bits 12/03/12 (Linux lat179 3.1.9-1.4-desktop) x86_64)
INZI473I Memory limitations: 'unlimited' for virtual memory and 'unlimited' for data segment
       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+
000001 GLOBAL CONNECT TO SAMPLE;
000002 UNLOAD TABLESPACE
000003 DB2 NO
000004 LOCK NO
000005 FLUSH BUFFERPOOLS NO
000006 SELECT * FROM EMPLOYEE;
000007 REPLACE STRING ('E' BY 'e' FOR (FIRSTNME), 'A' BY 'a' EXCEPT(FIRSTNME, JOB), 'R' BY 'r')
000008 FORMAT DEL;

INZU462I HPU control step start: 10:34:09.780.
INZU463I HPU control step end  : 10:34:09.783.
INZU464I HPU run step start    : 10:34:09.794.
"000010","CHRISTINe","I","HaaS","a00","3978","1995-01-01","PrES    ","18","F","1963-08-24","152750.00","1000.00","4220.00"
"000020","MICHAeL","L","THOMPSON","B01","3476","2003-10-10","MANAGEr ","18","M","1978-02-02","94250.00","800.00","3300.00"
"000030","SALLY","a","KWaN","C01","4738","2005-04-05","MANAGEr ","20","F","1971-05-11","98250.00","800.00","3060.00"
...
INZU410I HPU utility has unloaded 42 rows on lat179 host for I1010.EMPLOYEE in stdout.
INZU465I HPU run step end      : 10:34:09.799.
INZI441I HPU successfully ended: Real time -> 0m0.018693s
User time -> 0m0.018997s : Parent -> 0m0.018997s, Children -> 0m0.000000s
Syst time -> 0m0.015997s : Parent -> 0m0.015997s, Children -> 0m0.000000s

Using a custom SQL query:

[i1010@lat179(:) ~]$ db2hpu -f sysin -i i1010
INZM031I Optim High Performance Unload for Db2 06.01.00.001(121203) 
         64 bits 12/03/12 (Linux lat179 3.1.9-1.4-desktop x86_64)
INZI473I Memory limitations: 'unlimited' for virtual memory and 'unlimited' for data segment
       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+
000001 GLOBAL CONNECT TO SAMPLE;
000002 UNLOAD TABLESPACE
000003 DB2 NO
000004 LOCK NO
000005 FLUSH BUFFERPOOLS NO
000006 SELECT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE;
000007 REPLACE STRING ('A' BY 'a') 
000008 FORMAT DEL;

INZU462I HPU control step start: 10:37:33.573.
INZU463I HPU control step end  : 10:37:33.598.
INZU464I HPU run step start    : 10:37:33.600.
"000010","CHRISTINE","HaaS"
"000020","MICHaEL","THOMPSON"
"000030","SaLLY","KWaN"
...
INZU410I HPU utility has unloaded 42 rows on lat179 host for I1010.EMPLOYEE in stdout.
INZU465I HPU run step end      : 10:37:33.604.
INZI441I HPU successfully ended: Real time -> 0m0.030913s
User time -> 0m0.023996s : Parent -> 0m0.023996s, Children -> 0m0.000000s
Syst time -> 0m0.009998s : Parent -> 0m0.009998s, Children -> 0m0.000000s

Using the REPLACE STRING clause referring to an external file. The content of this external file is equivalent to the specification of the REPLACE STRING clause in the first example above:

[i1111@lat117]$ db2hpu -f SYSIN52 -i i1111
INZM031I Optim High Performance Unload for Db2 06.05.00.002.05(220405)
         64 bits 04/06/2022 (Linux lat117 3.10.0-957.21.3.el7.x86_64)
INZI473I Memory limitations: 'unlimited' for virtual memory and 'unlimited' for data segment
       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+
000001 GLOBAL CONNECT TO SAMPLE;
000002 UNLOAD TABLESPACE
000003 DB2 NO
000004 SELECT * FROM EMPLOYEE;
000005 REPLACE STRING IN "/home/i1111/Test/replace.txt"
000006 FORMAT DEL;

INZU462I HPU control step start: 04/06/2022 16:33:23.224.
INZU463I HPU control step end  : 04/06/2022 16:33:23.347.
INZU464I HPU run step start    : 04/06/2022 16:33:23.939.
"000010","CHRISTINe","I","HaaS","a00","3978",19950101,"PrES    ",18,"F",19630824,+0152750.00,+0001000.00,+0004220.00
"000020","MICHAeL","L","THOMPSON","B01","3476",20031010,"MANAGEr ",18,"M",19780202,+0094250.00,+0000800.00,+0003300.00
"000030","SALLY","a","KWaN","C01","4738",20050405,"MANAGEr ",20,"F",19710511,+0098250.00,+0000800.00,+0003060.00
...
INZU410I HPU utility has unloaded 42 rows on lat117 host for I1111.EMPLOYEE in stdout.
INZU465I HPU run step end      : 04/06/2022 16:33:23.949.
INZI441I HPU successfully ended: Real time -> 0m0.725769s
User time -> 0m0.065579s : Parent -> 0m0.065579s, Children -> 0m0.000000s
Syst time -> 0m0.023567s : Parent -> 0m0.023567s, Children -> 0m0.000000s
[i1111@lat117]$
[i1111@lat117]$ cat replace.txt
'E':'e';for:firstnme
'A':'a';except:firstnme,job
'R':'r';