CODEWITHSUNDEEP
sqldb2db2-luwdatastage
BHARATH RAJ
BHARATH RAJ

Reputation: 69

what is the equivalent of datastage convert function in DB2

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.

Upvotes: 1

Views: 675

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270331

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

Upvotes: 1

Pavol Adam
Pavol Adam

Reputation: 162

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

Upvotes: 1

Ray Wurlod
Ray Wurlod

Reputation: 851

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.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311798

You can use replace:

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

Upvotes: 0

Related Questions