Datastage - Extracting Numbers and Alphabets from a string - Convert function

 One of the queries we received - 

i have a record like this 

Source

empiempname

101raghu

102raja

103rajesh

target 

empid            empname

101              raghu

102               raja

103                rajesh

Here i should change the source as per the target how to do..??

Solution - Assumming Emp ID is always Numeric and EmpName will have only alpha characters.

Step 1 - EmpName - Extract Alpha Part - Use Convert function in Transformer

Convert("0123456789", "", Input.SourceString)

Example - Input.SourceString - 101raghu

Output - raghu

Step 2 - EmpID - Extract Numeric Part - Use Convert function in Transformer - Convert all characters extracted  in EmpName to empty in the original string. This will leave only the numeric characters which are the Emp ID

Convert(Convert("0123456789", "", Input.SourceString), "", Input.SourceString)

Example - Input.SourceString - 101raghu

Output - 101

The inner Convert function returns the alphabetic characters from the Source String  - raghu

The Outer Convert function then replaces these alpha characters - raghu from the Original Source string - 101raghu. This leaves the numeric part - 101.