topic Re: Regex to remove special characters in Alteryx Designer Desktop Discussions

Regex to remove special characters

DataIsFun — Thu, 05 Jan 2023 11:26:45 GMT

Hi community Happy new year,

I'm not to good with RegEx but I know it is needed in my use case. I have these company names but they contain special characters and i'm not sure how to remove them. I've attached a mini dummy sample of the data below.

Any assistance with this would be majorly appreciated

Name

Test Company 1 â€“Limited

Test Company 2â€™s Ltd

Oâ€™Test Hotel Limited

Re: Regex to remove special characters

FinnCharlton — Thu, 05 Jan 2023 11:35:07 GMT

Try REGEX_Replace([String],"[^a-z,A-Z,0-9,\s]","")

This gets rid of all characters that are not special letters, spaces or numbers.

Re: Regex to remove special characters

binu_acs — Thu, 05 Jan 2023 11:31:45 GMT

@DataIsFun One way of doing this

Re: Regex to remove special characters

Deano478 — Thu, 05 Jan 2023 12:18:36 GMT

@binu_acs I saw your solution and I'm just wondering how does your Regex work? 😀

Re: Regex to remove special characters

binu_acs — Thu, 05 Jan 2023 12:37:15 GMT

@Deano478the regex formula

REGEX_Replace([Name], '[^ -~]', '')

Remove any characters from the incoming field that are outside of the range of ASCII values from "space" to "~".

^ - represent the not operator

<space> - ~ characters with in the range space and ~

Below is the ASCII table where you can see the range 32(space) - 126(~), so any characters that fall other than this range will get replaced with ''

Re: Regex to remove special characters

Deano478 — Thu, 05 Jan 2023 12:50:32 GMT

@binu_acs that's actually very interesting to read I will definitely read more into regex to get more comfortable with it myself it seems very powerful when used the right way.

Re: Regex to remove special characters

malcorr — Mon, 13 Nov 2023 18:34:56 GMT

hello, i like this solution but i want to apply it in a text field that contains text in japanese, chinese and korean. How can i address that as well?

Re: Regex to remove special characters

binu_acs — Mon, 13 Nov 2023 21:26:04 GMT

@malcorr If you want to include the Japanese, Chinese, and Korean then find their corresponding ASCII characters and include those also in the regex expression, if you can provide some samples I can help you with this.