Regex

[Sarath27]

Hi All, 

Could you please help me with regex for below scenarios?

1. Identify all the special characters in a string excluding  ,-/&()  

2. Column named called Street_Address, where I need to write regex to identify any value which starts with #

 Example : #7, #Benny street, # 7 (space between # and number), # Benny street (space between # and alphabet)

#benny street   -  To be identified
#7 - invalid -To be identified
# 7 - invalidTo be identified
# benny street - To be identified
#7 benny street - Not to be identied, because this is valid one.

[IraWatt · Accepted solution]

Hey @Sarath27,

Your first scenario could be solved with regex like this:

([^a-zA-Z0-9,\-\/&\(\)]+)

This says capture any patterns of text which do not contain a-z 0-9 or any of the characters you specified. I had to backslash some of the like ( is \( because ( is a regex special character so needs to be escaped by a backslash.

If you want to learn more about Regex the community has some really quick interactive videos on getting to grips with it here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Parsing%20...

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[braveraj · Accepted solution]

Hi @Sarath27 

@IraWatt has you covered on identifying the special characters. If you want to match the pattern for the street address then the formula #\d+\s\w+.* will identify correct addresses and when used on a filter show you the ones that need to be fixed.

REGEX_Match([STREET_ADDR_TX], "#\d+\s\w+.*")  

[Sarath27 · Accepted solution]

Thank you @braveraj @IraWatt 

[IraWatt]

No worries @Sarath27, have a good one! 😄

[Sarath27]

@IraWatt Could you please help me with RegEx for the below?

Objective : To identify records which starts with #. I have used this RegEx (^[#][A-Za-z 0-9]), it works fine.

But the twist is, below records should  not be flagged, because they are good data  # (number) (address) is a good data, but if #(number alone) and #(address) should be flagged.

# 4 STAR

#4 MARS

Street

# 7000

# 16903

1223

apple

#4 MARS

# 4 STAR

#345

[Sarath27]

@IraWatt In the above records, even though the below two records starts with #, it should not be flagged.

#4 MARS

# 4 STAR

[IraWatt]

@Sarath27 can you share how you are using your regex? If you want to flag those rows this works: