CODEWITHSUNDEEP
sqlregexms-access-2013
JJJones_3860
JJJones_3860

Reputation: 1542

MS Access 2013 SQL wildcarding, regex-like syntax

The link below seems to indicate that "[a-zA-Z]*" should only match on zero or more alphabetic characters.

https://msdn.microsoft.com/EN-US/library/office/ff192499.aspx

However, in testing it seems to return results that I don't expect; for example, that contain spaces.

Select * from table1 where name like 'r[a-zA-Z]* e[a-zA-Z]*'

Should only return consecutive words in the name field where the first word starts with r and none or more alphabetic chars separated by a space and a second word that starts with e.

But executing this statement returns the following (some examples from the result set)

Is there a way to get the search to return exactly what I expect? What am I not understanding?

Upvotes: 1

Views: 41

Answers (1)

andrew
andrew

Reputation: 1816

It is not a regular expression. The * does not apply to the [a-zA-Z].

r[a-zA-Z]* e[a-zA-Z]* is interpreted as:

  • r
  • followed by any character a to z
  • followed by zero or more characters
  • followed by a space
  • followed by e
  • followed by any character a to z
  • followed by zero or more characters

Unfortunately you have to use something like this:

Select * from table1 where name like "r*" and mid(name , instr(name , " ") + 1, 1) = "e"

which isn't pretty and isn't precisely what you want.

Upvotes: 1

Related Questions