String Functions in DataStage
String Functions
- Left
- Right
- Substring
- Field
EX:-
- 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
Properties à Browse for file 
file = D :/ shilpa / invoice.txtGO to ColumnsLoading a text file with a single record
Column Name SQL Type Scale
Records Varchar 255
Now, click on view data
TransformerPropertiesCreate 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 |
| T | |
| T | |
| H |
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”
Output
Type data invoice num Custid bill date currency
H HWCNC INVC CID 454321 20080203 DDl
23409 CID 23409
H
H
H
Reading Comma Separated values of variable length
- Create an emp table with fields
E ID, E NAME, STATE
10, abc, ap
20, def, ap
30, ghi, ap
Create an EMP table with Spaces before, after in between, and some Special Characters
EID, E NAME, STATE
10, @abc, ap
20, def @, ap
30, ghi@, ap
Functions
- TRIMB - Removes after Space
- TRIMF - Removes after Space
- STRIP WHITE SPACES - Removes before, After, in-between spaces
- COMPACTWHITE SPACES
- Remove single Spaces
- IF Multiple Spaces are there, Removes in-between spaces and leaves space
EX:- MIND Q UEST MIND Q UEST
- TRIM – Removes Special Characters
To Eliminate Comma, Spaces, Special character using Functions:-
In — out — ds link 3
S.F Transform 1 Transform 2 Data set
↓
File = D: / Shilpa / emp.txt
Read it with Single Column (that is columns) (rec integer 255)
first line is column Name = True
Transformer 1:- (Removing Comma)
Click on OUTClick on load column Definitions SymbolSelect 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 Transformation -Data set
↓
Load an emp file
↓
TransformerPropertiesDrag into outCreate 3 New stage variablesUsing 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
(or)
- 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
DD Concat dd: dd:’/’ Contact DD:’/’ MM:’/’:YY
-
Task 2
Create a new column
COMM – VAL varchar 8
change the Hire data type to varchar
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 )
Compile and RUN
Parameters
EX:-
Int a = 6, b= 5, c
Compile-time
C= a+b
Here we are Passing values through Compile time Known as “Static Binding”.
Ex:- int a, b, c;
<in >>a>>b;
Run time
C = a+b
Passing Value at Run time is known as “Dynamic binding”
Now, we have used Static Binding, Now, we See Dynamic Binding using Parameterization.
Parameterization
It is a technique for passing values at run time.
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
Advantages:-
- Local parameters can be Reused
- We can not only pass/assign/value to parameter but can pass multiple values.
Using Parameters
Oracle Enterprise Transformer Data set Properties Read Mode = Table Table = EMPColumns à load Come to properties click on passwordInsert job parameterNew (or)Click on job properties SymbolParameters
| Parameter name | prompt | Type | Default value |
| UID | user id | String | Sc |
| PWD | password | Encrypted | Tiger |
| CS | Server | String | Oracle |
okclick on password on left sideinsert job parameter, Password = # PWD , User = #UID#, Remote Server =# CS#
Transformer properties Drag in to out click on ConstraintDouble click Right click job parameter New DNO Department list
10 (Enter) Add
20 (Enter)Add
30 (Enter)Add
okRight click job parameterSelect DNODNO = in. DEPT NO OkOk
Data set
- File àinsert job parameter à New
DRIVE IP String D:1
FOLDER FOLDER String Shilpal
TRG_FILE TARGET String Parameter. Ds
ok
# DRIVE ## FOLDER ## TRG _ FILE #
Compile
- 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 parameter Select UID, PWD, CSclick on create parameter Set parameter Set Name = Oracle – shilpaok Save it in Table Definitions ok Now, 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 RepositoryTable Definition oracle – shilpa Double click Values
Value file name UID PWD CS
Development SCOTT Tiger oracle
Testing abcd abcd renu
Production shilpa shilpa Star
- Ok
- Compile and RUN
Output
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?
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
Compile 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
Transformer
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
Output
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
Output
Data set
PartitioningàPartition type = HashàClick on cidàPerform sort, Stable, unique
For an in-depth understanding of DataStage click on
You liked the article?
Like : 0
Vote for difficulty
Current difficulty (Avg): Medium
About Author
Name
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.
Stay Updated
Get stories of change makers and innovators from the startup ecosystem in your inbox