String Functions in DataStage

  • Share this blog:

String Functions

  1. Left
  2. Right
  3. Substring
  4. Field


  • Mind Quest

L (7) = Mind Que

R(3) = Que                                  (on Sub String (5) 3)

R (L (7), 3)  

  • Mathe ndticks

R (L (7), 2) = ma (or)  L(R ()0,2) = ma  

  • Create an text file

HINVE 23409 CID 454321200802DOL

TPID1234                       5               12

TPID2345                        6              13

TPID3456                       7              14

  String Function  

Properties à Browse for file 7 file = D :/ shilpa / invoice.txt7GO to Columns7Loading a text file with a single record

Column Name                          SQL Type                     Scale

Records                                    Varchar                           255

Now, click on view data

Transformer7Properties7Create 2 new columns


  • Type char 1
  • Data varchar 100


  • Now drag in records to data


  • Now create a stage variable

type  —   0   —   varchar  —  “1”

  • Stage Variable left (in. records, i) type
  • Compile and RUN
  • Display all the 1st Characters:-


Type                          Data
H HINVC  23409CID0454321200802000L


Add Some More Column Names

In Voice no       Varchar            10
Cust id                 Varchar 10
Bill date              Varchar 10
Currency              Varchar 10
Product id            Varchar 10
Quantity               Varchar 10
Net amount         Varchar 10


Add new stage Variables

Right (left (in . records, 10), 9)  = in v no

Right (left (in . records, 19), 9)  = cid

Left(Right (in . records, 11), 8)  = bd

Right (in . records,  3)  = curr

Right (left (in . records, 8), 7)  = p id

Right (left (in . records, 17), 1)  = Quant

Right (left (in . records, 26), 2)  = Price  

Click on Constraint

Constraint     :   type = “H”


Type         data                 invoice num       Custid                bill date               currency

H                HWCNC               INVC            CID 454321          20080203               DDl

23409 CID                 23409




Reading Comma Separated values of variable length

  • Create an emp table with fields


10, abc, ap

20, def, ap

30, ghi, ap

Create an EMP table with Spaces before, after in between, and some Special Characters


10, @abc, ap

20, def @, ap

30, ghi@, ap


  • TRIMB - Removes after Space
  • TRIMF - Removes after Space
  • STRIP WHITE SPACES - Removes before, After, in-between spaces
  • Remove single Spaces
  • IF Multiple Spaces are there, Removes in-between spaces and leaves space


  • TRIM – Removes Special Characters

To Eliminate Comma, Spaces, Special character using Functions:-

  In     —   out          —         ds link 3  

S.F  7  Transform  1   7    Transform 2   7     Data set  


File = D: / Shilpa / emp.txt  

7Read it with Single Column  (that is columns)   (rec integer 255)

7first line is column Name = True

Transformer 1:-   (Removing Comma)

Click on OUT7Click on load column Definitions Symbol7Select EMP.txt  

Field  (in.  rec,’,’, 1)  = E id

Field  (in.  rec,’,’, 2)  = E Name

Field  (in.  rec,’,’, 3)  = State

[Syntax :- field  (% String %, %delimiter%,  % user name %)]àCompile and RUN

  E id               E NAME                State

10                   abc                          ap

20                    def                           ap

30                    ghi                          ap

Transformation 2:- (Eliminating Spaces, Special charities )

  • Properties
  • Up case (Trim (Strip white spaces (Dut E name),”@”)) = E name
  • Compile and RUN

  E id               E NAME                State

10                   abc                          ap

20                    def                           ap

ghi                   ap

Changing the format of date using Type conversion and Date and Time function 

  • Task 1 

We have the Date as   2008-01-03  (YYYY-MM-DD) 00:00:00 (Times stamp)Using field and type conversion functions change it to DD/MM/YY  

In                                                    out

Oracle enterprise     7   Transformation  7 -Data set  


Load an emp file


Transformer7Properties7Drag into out7Create 3 New stage variables7Using field functions

  Field (Times stamp To Date (in. Hire data),’_’,3) --DD

Field (Times stamp To Date (in. Hire data),’_’,2) --MM

Field (Times stamp To Date (in. Hire data),’_’,1) –YY


  • Using Type and Date conversion functions

Month Day from Date  (Times stamp To Date (in. Hire data)) –DD

Month  from Date  (Times stamp To Date (in. Hire data)) –MM

Month Year from Date  (Times stamp To Date (in. Hire data)) –YY


  Now in hiredate

  String function-change date

DD 7 Concat 7 dd: 7dd:’/’ 7 Contact 7 DD:’/’ MM:’/’:YY  

  • Task 2 

Create a new  column

COMM – VAL    varchar  8

7change the Hire data type to varchar

7 Create  another Stage variable Comm is varchar 10

It is null (in. comm) then “Null” else left  (in. comm., 2)

(that is, if Comm in NULL displays Null, else display 1st 2 characters )

7Compile and RUN



Int a = 6, b= 5, c                                              


C= a+b

7Here we are Passing values through Compile time Known as “Static Binding”.

Ex:-  int a, b, c;

<in >>a>>b;                    

Run time

C = a+b

7Passing Value at Run time is known as “Dynamic binding”

7Now, we have used Static Binding, Now, we  See Dynamic Binding using Parameterization.


It is a technique for passing values at run time.

7 It is more advantageous


  • Local parameters are job Specific p to 7.5 * 2
  • Global parameters are used in the entire project
  • Reusability in the project through a technique called Parameterization in 8.0.1

Inclined to build a profession as Datastage Developer? Then here is the blog post on, explore Datastage Training


  1. Local parameters can be Reused
  2. We can not only pass/assign/value to parameter but can pass multiple values.

Using Parameters

Oracle Enterprise             Transformer             Data set Properties 7 Read Mode = Table   Table = EMP7Columns à load 7 Come to properties 7 click on password7Insert job parameter7New   (or)7Click on job properties Symbol7Parameters  

Parameter name                prompt  Type                   Default value
UID                                         user id                   String                     Sc
PWD                                         password           Encrypted            Tiger
CS                                               Server                     String              Oracle

  ok7click on password on left side7insert job parameter, Password  = # PWD , User = #UID#, Remote Server  =# CS#

7Transformer 7 properties 7 Drag in to out 7 click on Constraint7Double click 7 Right click 7 job parameter 7 New   DNO                               Department               list             

10 (Enter) Add

20 (Enter)Add

30 (Enter)Add

7ok7Right click 7job parameter7Select DNO7DNO = in. DEPT NO7 Ok7Ok  

Data set 

  • File àinsert job parameter à New

  DRIVE                   IP                                  String                D:1

FOLDER                FOLDER                      String                  Shilpal

TRG_FILE            TARGET                        String                  Parameter. Ds




  • RUN
  • Dering RUN time it asks for Dept no

User id = Scott

Dept no = 20

  • Display data with Dept no = 20

Out put :-- During RUN  

Name Value
User id Scott
password Tiger
Server Oracle
dependent 30, 20, 10
input D:1/
folder Shilpal/
target Param . ds


  • Reuse:-  (To reuse in Multiple jobs)

Click on  job properties 7 parameter 7 Select UID, PWD, CS7click on create parameter Set 7 parameter Set Name = Oracle – shilpa7ok 7 Save it in Table Definitions 7 ok 7Now, whenever we create any other  job, we can directly call oracle – shilpa parameter set,

  • Oracle
  • Properties
  • Password = Set to Default

User = Set to Default

Remote Server = Set to Default

password = # oracle _shilpa. PWD #

User = # oracle – shilpa . UID #

Remote Server = # oracle – shilpa.cs #

  • Editing:-

View 7Repository7Table Definition 7oracle – shilpa 7 Double click 7 Values

Value file name                       UID                 PWD              CS

Development                            SCOTT             Tiger          oracle

Testing                                          abcd           abcd                renu

Production                                    shilpa            shilpa            Star

  • Ok
  • Compile and RUN


Name                                                           Value

Oracle – shilpa parameters                    (AS predefined )   testing production development

Name Value
User id Scott
password Tiger
Server Oracle
dependent 30, 20, 10
input D:1/
folder Shilpal/
target Param. ds

Transformation Execution order

  • Stage variables are Evaluated before the data is moved to the link (that is Transformer)
  • The derivation is Evaluated just before the data is moved to the target
  • In stage Variable, initialization is possible
  • Stage Variables are more advantages

  What is the Transformer Execution order? 

Transformer Execution Order  

 Calculating Experience

  Calculating experience  

Experience integer 7
Exp-months integer 7
Exp-days integer 7
Week-join integer 7
Quarter-join integer 7

  The task is to find out the experience in years, months, days, week of joining, Quarter Of joining

Create 2 stage variable

Exp          integer         7

Month     integer         7

Exp = year from Date  (current Date ()) –  Year from Date  (Times stamp to date (in. Hire date))

Month = Month from Date  (Times stamp to date (in. Hire date))

  • Now - - > exp = experience
  • Exp * 12 = exp – month
  • Days Since from Date (current Date) Times stamp to date (in. Hire date) = Exp- days
  • Year week from Date (Times stamp to date (in. Hire date)) = week – join
  • If month < =3 then1 else if Month >=4 and Month <=6

Then 2 else if month > = 7 and Month <=8 then 3 else 4

  7Compile and RUN

Transformation with Sorting 

Create a file  

E id E name Account
111 Suman Savings
222 Kumar loans
111 Suman current
333 uma loans
111 suman credit
222 Kumar savings
333 uma insurance
111 suman insurance
222 Kumar current

  Case 1



Stage Variables

Previous key        0      Integer/ Tiny int      1

New key             “N”     char                          1

Accents              “Y”       Varchar               255  

Map the key change to prev key

In. key change –prev key

If prev key = 1 then “y” then in. account           --accents

Else accounts:”,”: in. account  

  • Create a new Column:- 1 ACNT – ALL
  • Drag accents to ACNT – ALL
  • Accents - ACNT – ALL
  • Now, Compile and RUN


  E ID          E NAME                   Account             key                a  

Case 2

Sequential file -----Sort----Transformer------Sort--------Data set  

Create a new Stage variable 

If new accent  =”y” then 1 else cnt+1 -- - >cnt

  • Sort

Key  = Count / Eid

Sort order = Descending


Data set

PartitioningàPartition type = HashàClick on cidàPerform sort, Stable, unique

For an in-depth understanding of DataStage click on

About Author
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.