what is the equivalent of datastage convert function in DB2

Question

Convert function return a copy of variable with every occurrence of specified characters in variable replaced with other specified characters. Every time a character to be converted appears in variable, it is replaced by the replacement character. example:

A="NOW IS THE TIME"
PRINT A
A=CONVERT('TI','XY',A)
PRINT A
A=CONVERT('XY','T',A)
PRINT A

OUTPUT

NOW IS THE TIME
NOW YS XHE XYME
NOW S THE TME

How to achieve this in db2 query.

Answer 1

I think the equivalent is translate():

translate(A, 'XY', 'TI')

Here is a db<>fiddle.

This is not exactly equivalent. Unfortunately, translate() in DB2 does not remove characters (translate() in Oracle does) by default. Instead, DB2 replaces them with spaces or an explicit pad character when the "to" string is shorter than the "from" string. It only removes characters when the pad has a length of zero.

select translate(translate('NOW IS THE TIME', 'XY', 'TI'), 'T', 'XY', '')
from sysibm.sysdummy1

Answer 2

TRANSLATE is a fully functional equivalent in DB2 LUW (e.g. 9.7): https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000862.html

SET A = 'NOW IS THE TIME';
--PRINT A
SET A = TRANSLATE(A, 'XY', 'TI');
--PRINT A
SET A = TRANSLATE(A, 'T', 'XY', ''); --empty padding crucial!
--PRINT A

Answer 3

You can use replace:

REPLACE(A, 'TI', 'XY')

Answer 4

The REPLACE function in DB2 replaces all occurrences of search-string in source-string with replace-string. If search-string is not found in source-string, source-string is returned unchanged. https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_bif_replace.html Usefully, DataStage also has a Replace() function, with the same functionality.