Extracting 4 digit number from a Character Column

[rkumar28]

Hi,

I have to extract ONLY the four digit number in a column. I have a char(5) column (in a flat file) that has numbers from 1 digits to 5 digits. I have to extract only those rows from this file that contains 4 digits number and load into the target.

Thanks for you help

[ray.wurlod]

You also have to specify what you want to happen if there isn't a four digit number in the input column.
Also, what do you want to do if the CHAR(5) column contains five digits?
You can use pattern matching to determine whether there is a four digit number.
For example, you might set up the following constraint expression:

Code: Select all

InLink.ColName Matches "0X4N0X" And Not(InLink.ColName Matches "5N")

or, depending on your data:

Code: Select all

Trim(InLink.ColName) Matches "4N"

[rkumar28]

Hi,

Thanks for the responding to my question:

I tried your suggestion. But I got all the 100,000 rows with the value of 0 and 1. 1 is returned for the 4 digit number and 0 for all other digits.
I just need to extract only those rows that has only 4 digit number in a column. But currently it extracts all the rows and put 1 and 0 in that column.

I am little new to DataStage. I will really appreciate any further advice.

Thanks....

You also have to specify what you want to happen if there isn't a four digit number in the input column.
Also, what do you want to do if the CHAR(5) column contains five digits?
You can use pattern matching to determine whether there is a four digit number.
For example, you might set up the following constraint expression:

Code: Select all

InLink.ColName Matches "0X4N0X" And Not(InLink.ColName Matches "5N")

or, depending on your data:

Code: Select all

Trim(InLink.ColName) Matches "4N"

[ray.wurlod]

What I suggested was to add a Constraint to the output link, so that only the four digit strings would get through.

You've used the expression as the output column derivation, rather than as a column constraint.
To get in to the constraints grid, double click on the grey "Constraints" area at the top of the link definition.

Because you're only letting through the four digit numbers, you can get away with deriving the output directly from the input.