MSAccess Query a string matching a pattern

Question

I have a table with a string field containing location information. I want to be able to query this table and retrieve all of the tags matching the format xxxxxxAA where xxxxxx is a 6-digit number and AA is two alphabetic characters.

Is there a method of querying this using SQL or is this something that I need to do in VBA?

Sample data:

BGS5 PM RGP5
022051PM
022201PM
030539PM
WAS3N
179546MM

And I want to return the following without knowing the values:

022051PM
022201PM
030539PM
179546MM

thanks in advance Jason

HansUp · Accepted Answer · 2013-08-04 20:00:01Z

1

You can use a query with a Like comparison in the WHERE clause.

SELECT y.text_field
FROM YourTable AS y
WHERE y.text_field Like '######[A-Z][A-Z]'

The # matches a digit.

[A-Z] matches one character from a character class consisting of only letters. That character class is actually upper case letters. However, the comparison is case-insensitive, so will match lower case letters, too.

edited Aug 4, 2013 at 20:00

answered Aug 4, 2013 at 4:35

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rachel Hettinger Over a year ago

If your source data is a native MS Access table, query criteria are always case-insensitive. Option Compare Binary only applies to VBA code such as If text_field Like "######[A-Z][A-Z]" Then....

Collectives™ on Stack Overflow

MSAccess Query a string matching a pattern

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related