topic Removing Special Characters from an Integer column in Talend Studio

Removing Special Characters from an Integer column

AV2022 — Fri, 15 Nov 2024 21:55:07 GMT

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

Re: Removing Special Characters from an Integer column

Anonymous — Tue, 11 Apr 2023 00:16:14 GMT

Hello @Anand V ,

You can use String replaceAll method to do it like:

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

Re: Removing Special Characters from an Integer column

AV2022 — Tue, 11 Apr 2023 05:59:44 GMT

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

Re: Removing Special Characters from an Integer column

Anonymous — Tue, 11 Apr 2023 06:14:15 GMT

Hello @Anand V ,

Please change the expression to

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

Re: Removing Special Characters from an Integer column

AV2022 — Tue, 11 Apr 2023 06:32:00 GMT

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

Re: Removing Special Characters from an Integer column

AV2022 — Tue, 11 Apr 2023 09:16:52 GMT

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