3

I am trying to remove text from a specific column and only keep the numeric values. I tried to use a derived column and also tried to search for the answer, but did not find the solution.

For example, the table looks like this:

order_number
1001
1002 k
1003
text
1004
aa 1007

I hope somebody could help me with this. Thanks in advance

This is the error I get:

screenshot error

Hadi
  • 36,233
  • 13
  • 65
  • 124
user7418776
  • 57
  • 7

1 Answers1

2

You could use a script component (make sure to add an output column (outordernumber)):

if(!Row.ordernumber_IsNull){
    Row.outordernumber = System.Text.RegularExpressions.Regex.Replace(Row.ordernumber, "[^0-9]", "")
}else{
    Row.outordernumber_IsNull = true;
}

I think you missed this step:

enter image description here

Package Result:

enter image description here

Hadi
  • 36,233
  • 13
  • 65
  • 124
KeithL
  • 5,348
  • 3
  • 19
  • 25
  • I tried this, but i keep getting an error: CS1061 'Input0Buffer' does not contain a definition for 'order_number' and no accessible extension method 'order_number' accepting a first argument of type 'Input0Buffer' could be found (are you missing a using directive or an assembly reference?) – user7418776 Jun 18 '20 at 14:14
  • also. keep in mind that these are strings and not numbers and that one that didn't have any numbers is an empty string ("") which will not convert direct to a number – KeithL Jun 18 '20 at 16:59
  • thank you. I got a little bit farther. When i try to run the component, it gives me error: [Script Component [91]] Error: System.ArgumentNullException: Value cannot be null. – user7418776 Jun 19 '20 at 16:33
  • It means you have nulls in your data which you didnt provide in your sample. If(row.colname.isnullorempty){row.colname="";}else{original logic} – KeithL Jun 20 '20 at 17:12
  • i tried the if else statement but could not get it to work, because if, else and isnullorempty could not get it recognized. I tried to bypass it by replacing all the nulls with "a", with derived collumn but still got the null values error i showed in the print screen before. – user7418776 Jun 22 '20 at 18:27
  • @user7418776 make sure to add a new output column in the script component, also you should check if the input column is null before applying any transformation. Check my update to this answer – Hadi Jun 22 '20 at 19:09