Removing Special Characters from an Integer column

[AV2022]

Hello Team,

I am trying to do a data import from Access to Oracle where one of the input column in Access is having Integers. The output column in Oracle Database is defined with Data type "Number" with a precision "2"

The problem I am facing here is the input integer column has some integer values with special characters, extra spacing and some integers are 3 digit integers. For example "1

°", " 11", "111".

I am not able to find out the right expression in expression builder which can remove the special characters, spaces and 3 digit values. I want my output data to be clean with integers with a max precision of 2.

Kindly suggest me how i can fix this issue.

Thanks

Anand V

[Anonymous]

Hello @Anand V​ ,

You can use String replaceAll method to do it like:

str.replaceAll("\\s|°", "")

[AV2022]

Hello Achen,

Thanks for your input, but unfortunately the given solution is not working for me. Even after creating replaceall expression the output is still the same, please find the attached screenshot of the tmap and Output. Please let me know if any further changes needs to be done in the expression or not.

Thanks

Anand

[Anonymous]

Hello @Anand V​ ,

Please change the expression to

str.replaceAll("\\s|°|N/A|null", "")

[AV2022]

Hello Achen,

Thanks alot for your help, the above expression worked 90 percent. My only concern now is i dont want values which are greater than 2 digits, in the output i still have values which are 3 digits (eg: 111). How can i remove those 3 digit numbers.

Thanks

Anand

[AV2022]

Hello Achen,

I found the Solution for restricting 3 digit value using tSchemaCompliancecheck component where I can restrict the input length for the Schema columns. Thank you so much for your help, much appreciated.

Thanks

Anand