Monday, December 31, 2012

Oracle SQL replace string with another

example :-

SELECT J.NOSTAF, J.NOKP, J.NOKPR, J.NOKPT, J.NAMA
FROM (SELECT A.NOSTAF, A.NOKP, REPLACE(A.NOKP, ' ', '') AS NOKPR,
              TRANSLATE(A.NOKP, 'A', 'B') AS NOKPT,
              A.NAMA FROM TANGGUNGAN A) J
WHERE J.NOSTAF LIKE '%';


//REPLACE ([string], [string to replace], [replacement string] )
//TRANSLATE([string], [string to replace], [replacement string] )

with oracle 10g

original data set sample :-

NOSTAF, NOKP, NAMA,
01, A 1111111, ABU
02, A2222222, BAN
03, A3 3 3 3 333, COT

illustrated output :-

NOSTAFNOKPNOKPRNOKPTNAMA
01A 1111111A1111111B1111111ABU
02A2222222A2222222B2222222BAN
03A3 3 3 3333A3333333B3333333COT

No comments: