How do to pattern matching in stage variable

[hcdiss]

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.

[shamail]

use string search functions to find if the required string is present in the incoming string.

[Sainath.Srinivasan]

You can do a regular expression match in Oracle itself (in source SQL).

In DataStage, you can do an Index()

[hcdiss]

[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.

[Sainath.Srinivasan]

With no disrespect, did you try to search? There are loads of examples.

[ray.wurlod]

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 ""

[jhmckeever]

You said "like owner ,occupier,customer ..."

How many of these values do you need to look for and substitute?

J.

[hcdiss]

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.

[Kryt0n]

Title - if count(customer_name, ',') = 0 then customer_name else ''

[ray.wurlod]

That's a totally different question, not a "changed requirement".

Please begin a new thread, so future searchers are not confused.

[hcdiss]

[quote="Kryt0n"]Title - if count(customer_name, ',') = 0 then customer_name else ''[/quote]

Thanks Kryton....i had already tried the same yesterday and this was not working.
but today somehow this is working!!!!!!


Will close the thread now.