RegEx help for matching alpha AND numeric

[whitkrieng]

Hi all,

I've given up trying to test the right RegEx syntax for cleaning some raw Address values.  What I would to do is match non-standard values and NULL them.  So I would like to RegEx match any record that has an alpha AND numeric, not either or.  So Example is:

Address	Clean_Address
pobox	NULL
123	NULL
pobox 123	pobox 123
123 Sesame St.	123 Sesame St.
.	NULL
needed	NULL

Any help would be greatly appreciated. Thank you!

[grazitti_sapna · Accepted solution]

@whitkrieng , Please find my solution and let me know if it helped.

[flying008]

Hi, @whitkrieng 

The formula for [Clean_Address] :

IIF(REGEX_CountMatches([Address], "\d") * REGEX_CountMatches([Address], "[a-zA-Z]") !=0,[Address], Null())

******

If it can help you , please mark it as a solution and give a like to here. 

[binuacs]

@whitkrieng One way of doing this

IIF(REGEX_CountMatches([Address], '\s') > 0, [Address],Null())

[whitkrieng]

@grazitti_sapna thank you so much

I was wondering if you could help translate for me what each part of the expression means just for my own learning purposes.  It's honestly been a bit of a struggle deciphering what works for RegEx: 

\d+\s[[:alpha:]]+.*

[[:alpha:]]+.*\s\d+

Thank you again!

[grazitti_sapna]

@whitkrieng , In the first expression it is matching 1 or more digits followed with a single space and 1 or more alphanumeric characters and then any other characters in a string with .*.

Same goes for second string, it will look for 1 or more alphanumeric characters followed with space and then 1 or more digits.

[whitkrieng]

@grazitti_sapna Thank you for your response.  I realized in one scenario say it were just one string condensed together, it doesn't NULL the value.  Sorry I didn't put it in the original examples.  

For in this case: 

123SesameSt.

With no spaces, it appears to NULL. I would want to retain this due to the fact there is an alpha AND a numeric character in it. 

Thank you again!