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
Extracting 4 digit number from a Character Column
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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:
or, depending on your data:
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")
Code: Select all
Trim(InLink.ColName) Matches "4N"
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.
4 digit number
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....
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....
ray.wurlod wrote: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:or, depending on your data:Code: Select all
InLink.ColName Matches "0X4N0X" And Not(InLink.ColName Matches "5N")
Code: Select all
Trim(InLink.ColName) Matches "4N"
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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.