3/18/16

How to remove extra delimiters using DataStage

Input Sample Data:

Order_Number
----------------
C67330672,
C97652762,C67330672,
C67330672,C67330672,C67330672,
C38750605
C43446335,C21659445
C21659445,C21659445,C21659445

To Eliminate extra delimiters [commas ","] in above first 3 rows used below process.

Defined Transformer Stage Variables:

svDCOUNT =  Dcount(Order_Number,",")
     svFIELD =  Field(Order_Number,",", svDCOUNT)
  svISNULL =  If Trim(svFIELD)='' Then 0 Else 1
svLENGTH =  Len(Order_Number)-1


Then in Derivation :

If svISNULL=0 Then Order_Number[1, svLENGTH] Else Order_Number


Output:
----------------------

Order_Number
----------------
C67330672
C97652762,C67330672
C67330672,C67330672,C67330672
C38750605
C43446335,C21659445
C21659445,C21659445,C21659445