How do to pattern matching in stage variable
Moderators: chulett, rschirm, roy
How do to pattern matching in stage variable
Hi,
My input table consists of Customer titles like owner ,occupier,customer with combination of "the" either "a"
Customer Title
OCCUPIER
OCCUPIER,OWNER
THE CUSTOMER
A CUSTOMER
EXECUTIVE
EXECUTIVE THE
Output requires -->
I have to search the customer table and suppose if it contains the word "OCCUPIER" OR "CUSTOMER" i have to write "THE OWNER",if it contains the word "EXECUTIVE" i have to populate " The executive".
Title
The owner
The owner
The owner
The owner
The Executive
The Executive
I tried in stage variable with condition :
if customer title = '%occu%' or '%cust%' then 'the owner' but nothing works.
only blank is populated.
Any suggestions.
My input table consists of Customer titles like owner ,occupier,customer with combination of "the" either "a"
Customer Title
OCCUPIER
OCCUPIER,OWNER
THE CUSTOMER
A CUSTOMER
EXECUTIVE
EXECUTIVE THE
Output requires -->
I have to search the customer table and suppose if it contains the word "OCCUPIER" OR "CUSTOMER" i have to write "THE OWNER",if it contains the word "EXECUTIVE" i have to populate " The executive".
Title
The owner
The owner
The owner
The owner
The Executive
The Executive
I tried in stage variable with condition :
if customer title = '%occu%' or '%cust%' then 'the owner' but nothing works.
only blank is populated.
Any suggestions.
hcdiss
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
[quote="Sainath.Srinivasan"]You can do a regular expression match in Oracle itself (in source SQL).
In DataStage, you can do an Index()[/quote]
can you give me any example of INDEX function.as i have already build the job i dont want to change anything on input end due to some other constraints as well.
In DataStage, you can do an Index()[/quote]
can you give me any example of INDEX function.as i have already build the job i dont want to change anything on input end due to some other constraints as well.
hcdiss
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Begin by creating a stage variable in which you apply an Upcase() conversion to the incoming customer title. This will allow your comparisons to be case-insensitive. A suitable column derivation would then be:
Code: Select all
If Index(svCustomerTitle, "OCCUPIER", 1) > 0 Or Index(svCustomerTitle, "CUSTOMER", 1) > 0 Then "The Owner" Else If Index(svCustomerTitle, "EXECUTIVE", 1) > 0 Then "The Executive" Else ""
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 301
- Joined: Thu Jul 14, 2005 10:27 am
- Location: Melbourne, Australia
- Contact:
You said "like owner ,occupier,customer ..."
How many of these values do you need to look for and substitute?
J.
How many of these values do you need to look for and substitute?
J.
<b>John McKeever</b>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Data Migrators
<b><a href="https://www.mettleci.com">MettleCI</a> - DevOps for DataStage</b>
<a href="http://www.datamigrators.com/"><img src="https://www.datamigrators.com/assets/im ... l.png"></a>
Requirement Changes
Thanks everyone for the replies.
Now requirement has changes.
INPUT--->
CUSTOMER_NAME CHAR(60)
hadley,chris,mr
the occupier
customer
keith,ray a ,miss
OUTPUT REQUIRED IS
TITLE | FIRSTNAME | MIDDLE NAME | SURNAME
mr | chris | | hadley
the occupier | | |
customer | | |
miss |ray |a | keith
Now to populate this what i have tried is
For Title ---> field(CUSTOMER_NAME,','3)
For FirstName-----> field(CUSTOMER_NAME,','2)
For MiddleName -----> field(FirstName,' ',2)
For Surname ----------> field(FirstName,',',1)
Now when the record has 3 commas this gets populated correctly.
But for record which has only one word or are without comma the whole word should be populated in TITLE field wich is not happening.
Some times its blank ,sometimes its getting populated in Firstname ,Lastname column.
I tried a stage variable which stores The TITLE AND if TITLE IS populated as BLANK then CUSTOMER_NAME should be populated but not working.
which means if CUSTOMER NAME = THE OCCUPIER obviously TITLE will be populated as BLANK and hence it should chek for blank and if BLANK then populate CUSTOMER_NAME = THE OCCUPIER.
Also tried len(trim(CUSTOMER_NAME) = '0' then CUSTOMER_NAME else TITLE ; not working
Help please.
Now requirement has changes.
INPUT--->
CUSTOMER_NAME CHAR(60)
hadley,chris,mr
the occupier
customer
keith,ray a ,miss
OUTPUT REQUIRED IS
TITLE | FIRSTNAME | MIDDLE NAME | SURNAME
mr | chris | | hadley
the occupier | | |
customer | | |
miss |ray |a | keith
Now to populate this what i have tried is
For Title ---> field(CUSTOMER_NAME,','3)
For FirstName-----> field(CUSTOMER_NAME,','2)
For MiddleName -----> field(FirstName,' ',2)
For Surname ----------> field(FirstName,',',1)
Now when the record has 3 commas this gets populated correctly.
But for record which has only one word or are without comma the whole word should be populated in TITLE field wich is not happening.
Some times its blank ,sometimes its getting populated in Firstname ,Lastname column.
I tried a stage variable which stores The TITLE AND if TITLE IS populated as BLANK then CUSTOMER_NAME should be populated but not working.
which means if CUSTOMER NAME = THE OCCUPIER obviously TITLE will be populated as BLANK and hence it should chek for blank and if BLANK then populate CUSTOMER_NAME = THE OCCUPIER.
Also tried len(trim(CUSTOMER_NAME) = '0' then CUSTOMER_NAME else TITLE ; not working
Help please.
hcdiss
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: