pattern matching and exclusion

  • Thread starter Thread starter ChasW
  • Start date Start date
C

ChasW

I want to query for individual characters in a field and exclude other
possible characters.

Right now I am using for inclusion:
WHERE [tbl].[column] LIKE "*" & "A" & "*"

and for exclusion:
WHERE [tbl].[column] NOT LIKE "*" & "B" & "*"

If it was given that there were up to 7 possible different characters
that could be in this field, what would be the most efficient way to
query for the presence of any number of those 7, while excluding the
remaining possibilities.

For example:
The query will look for fields with any combination of A and/or B
and/or C.
It will need to exclude any fields that have D, E, F, G

The characters could be in this field in any order.

What is a more efficient way than having the query be 3 LIKEs and 4
NOT LIKEs, assuming there is one ?

Thanks in advance,
Chas
 
Dear Chas:

WHERE tbl.column LIKE "*[ABC]*" AND NOT tbl.column LIKE "*[DEFG]*"

Now this will exclude strings that don't contain at least one A B or C, and
also exclude strings that contain D E F or G.

Is that what you want? Does this work for you?

Tom Ellison
 
Tom said:
Dear Chas:

WHERE tbl.column LIKE "*[ABC]*" AND NOT tbl.column LIKE "*[DEFG]*"

Now this will exclude strings that don't contain at least one A B or C, and
also exclude strings that contain D E F or G.

Is that what you want? Does this work for you?

Tom Ellison
Click to expand...

Hi Tom,

Possibly. Aren't the ABC and DEFG, as you have them, evaluated as
order-sensitive strings? Or does placing the characters between the []
cause them to be evaluated individually? If so, then that looks like
what I need.

Chas
 
The square brackets are being used to signify pattern-matching which means
that your string only has to have 1 character in the list enclosed by [] to
be selected.

See Access Help topic "About using wildcard characters".

--
HTH
Van T. Dinh
MVP (Access)



ChasW said:
Tom said:
Dear Chas:

WHERE tbl.column LIKE "*[ABC]*" AND NOT tbl.column LIKE "*[DEFG]*"

Now this will exclude strings that don't contain at least one A B or C,
and
also exclude strings that contain D E F or G.

Is that what you want? Does this work for you?

Tom Ellison
Click to expand...

Hi Tom,

Possibly. Aren't the ABC and DEFG, as you have them, evaluated as
order-sensitive strings? Or does placing the characters between the []
cause them to be evaluated individually? If so, then that looks like
what I need.

Chas
Click to expand...
 
The thing in the bracket is evaluated as "any one of these characters." Try
it, you may like it.

Tom Ellison


ChasW said:
Tom said:
Dear Chas:

WHERE tbl.column LIKE "*[ABC]*" AND NOT tbl.column LIKE "*[DEFG]*"

Now this will exclude strings that don't contain at least one A B or C,
and
also exclude strings that contain D E F or G.

Is that what you want? Does this work for you?

Tom Ellison
Click to expand...

Hi Tom,

Possibly. Aren't the ABC and DEFG, as you have them, evaluated as
order-sensitive strings? Or does placing the characters between the []
cause them to be evaluated individually? If so, then that looks like
what I need.

Chas
Click to expand...
 
Back
Top