Need Help with a Case-Sensitive 'Like' Query

[CL]

I want to query a number of database fields to return only those
records that contain a specific string, and I want the match to be
case-sensitive.

I am currently using 'LIKE' in a query that looks something like this:

SELECT *
FROM tblArtistInfo
WHERE tblArtistInfo.ArtistName LIKE '%Joe Blow%'

This query returns results when it encounters 'Joe Blow' or 'Joe blow'.

How do I make sure my results are case sensitive and only return
matches for 'Joe Blow' and NOT 'joe blow', 'Joe blow', or 'joe Blow'?

 

[Ken Snell [MVP]]

You'll need to use a criterion that contains the StrComp function, using the
binary comparison setting. Check it out in Help file.

 

[John Vinson]

I want to query a number of database fields to return only those
records that contain a specific string, and I want the match to be
case-sensitive.

I am currently using 'LIKE' in a query that looks something like this:

SELECT *
FROM tblArtistInfo
WHERE tblArtistInfo.ArtistName LIKE '%Joe Blow%'

This query returns results when it encounters 'Joe Blow' or 'Joe blow'.

How do I make sure my results are case sensitive and only return
matches for 'Joe Blow' and NOT 'joe blow', 'Joe blow', or 'joe Blow'?

You'll need to use the InStr function with its optional Compare
operand:

SELECT *
FROM tblArtistInfo
WHERE InStr(1, [tblArtistInfo].[ArtistName], "Joe Blow", 0) > 0;

See the VBA online help for InStr.

John W. Vinson[MVP]

 

[CL]

Thanks for the help!
John your method seems to work just fine.

I found more details on the InStr function here:
MSDN Home > MSDN Library > Web Development > Scripting > Windows
Script Technologies > VBScript > Reference > Functions

Do you recommend any other resources? I'd like to learn the difference
between the binary and text comparisons InStr uses.

 

[John Vinson]

Do you recommend any other resources? I'd like to learn the difference
between the binary and text comparisons InStr uses.

I meant to say *offline* help... <g> Just the Help file topic for
Instr, but I would guess you got the same content.

Binary comparison is bit by bit, so that N and n and ñ are all
distinct characters. Character comparison collapses case and uses the
Windows language settings to decide which characters are "the same".

John W. Vinson[MVP]