Case sensitive query

[John Dumay]

Hi,

I want to create a query that returns a case sensitive response.

In my tabke on field can contain the strings "other" or "Other" (along
wither other strings). What I want to do is only return the records that
match "other" not "Other".

Can anyone help?

Regards,

John

 

[John Spencer]

You can use the StrComp function

Field: StrComp([SomeField,"other",0)
Criteria: = 0

That M_I_G_H_T be faster if you add another column. With luck this will
whittle down the fields to compare first. (speculation on my part)

Field: SomeField
Criteria: = "OTHER"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Dirk Goldgar]

Hi,

I want to create a query that returns a case sensitive response.

In my tabke on field can contain the strings "other" or "Other" (along
wither other strings). What I want to do is only return the records that
match "other" not "Other".

You can use the StrComp function in your query with an optional argument to
force a binary comparison. For example,

SELECT * FROM MyTable WHERE StrComp(SomeField, "other", 0) = 0

 

[John Dumay]

Hi Dick,

Works like a charm.

Thanks,

John

You can use the StrComp function in your query with an optional argument to
force a binary comparison. For example,

SELECT * FROM MyTable WHERE StrComp(SomeField, "other", 0) = 0

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

That M_I_G_H_T be faster if you add another column. With luck this will
whittle down the fields to compare first. (speculation on my part)

Field: SomeField
Criteria: = "OTHER"

That's a very interesting and clever idea, John. I'll bet you're right,
though I haven't tested it yet. I'd expect the query optimizer to filter
first on the case-insensitive comparison, which it can do without ever
calling out to VB, and only call VB function for the records that pass the
filter -- probably saving a lot of overhead. I'm going to run some
benchmarks.

 

[Dirk Goldgar]

That's a very interesting and clever idea, John. I'll bet you're right,
though I haven't tested it yet. I'd expect the query optimizer to filter
first on the case-insensitive comparison, which it can do without ever
calling out to VB, and only call VB function for the records that pass the
filter -- probably saving a lot of overhead. I'm going to run some
benchmarks.

I just did, and found that it made a big difference if the field is
unindexed -- about a 40% speed improvement -- and a HUGE difference if the
field is indexed: about an 80% speed improvement! John, you're a genius.

 

[a a r o n _ k e m p f]

or, you could use an enterprise level database that easily supports
different codepages for different objects.

you could make one table case sensitive, and another not case
sensitive.

Jet just isn't powerful enough to do what you're asking (with
acceptable performance)

 

[david]

Jet does case sensitive fields. Most users just aren't
powerful enough to set them up.

Jet also easily supports different codepages for different
objects. Most users just aren't powerful enough to set
them up.

I agree that when you have an enterprise level problem,
you should use an enterprise-level database engine,
but obviously for trivial problems like case-sensitive
fields and different codepages that is not necessary.

(david)

or, you could use an enterprise level database that easily supports
different codepages for different objects.

you could make one table case sensitive, and another not case
sensitive.

Jet just isn't powerful enough to do what you're asking (with
acceptable performance)

 

[a a r o n . k e m p f]

I'm not sure I agree.

BUILD IT NICE OR BUILD IT TWICE.
Your strConv comparison does a scan, SQL Server isn't so stupid.

 

[John Spencer]

WOW! I'm going to mark this. Dirk Goldgar called me a genius.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[david]

That wasn't my strConv comparison. In Jet if you want
case-sensitive indexes, you can use a binary-case text field.

(david)

message
I'm not sure I agree.

BUILD IT NICE OR BUILD IT TWICE.
Your strConv comparison does a scan, SQL Server isn't so stupid.

 

[a a r o n . k e m p f]

where? how? I don't see this in the gui!

That wasn't my strConv comparison. In Jet if you want
case-sensitive indexes, you can use a binary-case text field.

(david)

messageI'm not sure I agree.

BUILD IT NICE OR BUILD IT TWICE.
Your strConv comparison does a scan, SQL Server isn't so stupid.








- Show quoted text -

 

[a a r o n _ k e m p f]

100 straws together broke the camels back.

I for one-- was sick and tired of Jet crashing; and a crappy GUI for
designing queries.
Stored Procedures are more powerful than 100 jet programmers.

Maybe if you were educated enough to spell SQL, then maybe you would
realize that the future of MS Access lies inside the belly of SQL
Server- the worlds most popular database.

 

[GenlAccess]

where? how? I don't see this in the gui!

It's there. When your special ed class is dismissed for the day, go back and
look some more for "database compare" which will include "text compare" and
"binary compare". If you can't find it, as the Special Ed teaching assistant
for help.

Gen'l Access

 

[GenlAccess]

Maybe if you were educated enough to spell
SQL, then maybe you would realize that the
future of MS Access lies inside the belly of SQL
Server- the worlds most popular database.

Maybe when your special ed class gets along to reading, you'll be able to
research and discover that Access with Jet is the world's most popular
database, and that Access with ACE is coming along fast. Anthromorphising
SQL Server won't get you anywhere... it doesn't have a digestive system.

Gen'l Access

 

[a a r o n . k e m p f]

wow, that's funny-- I don't see anything about what you're talking
about.

Are you talking specifically about Access 2007?
I just don't see anything here like you're referring to. I type in
database compare, and I see these 6 topics

About an Access project
A comparison of global, local, and anonymous replicas
Publish Web pages
Comparison of functions between Visual Basic for Applications and SQL
Server Transact-SQL
About differences between data types in an Access database and Access
project
Create a select or crosstab query

Using StrConv means that you're doing a table scan for the most part,
so it's basically unusable.

 

[david]

No, it's no in the gui :~) That would be too tempting for
naive users. You have to use DDL to get binary-case text
fields. I'm not a big advocate of binary-case text fields myself:
If you think john and John are different people, it normally
indicates a design problem, not a technical problem.

(david)

message
where? how? I don't see this in the gui!

 

[a a r o n . k e m p f]

so what you're saying-- is that Access doesn't support it.

thanks

-Aaron

 

[a a r o n . k e m p f]

Not in the gui means it's not really practical to use.

Just like sprocs in Jet.
It's just not there.

If Jet supported a single File, Connection like ADP did-- it might be
practical.
But until then-- it's just too much of a hassle (because connection
strings need to be set on SQL passthrough also; I mean what a pain!!

 

[a a r o n . k e m p f]

performance is a key reason, IMHO.

Binary comparisons are always faster.

If everything that you use-- is upper case- it would make sense to use
a binary comparison.

of course, SQL Server natively supports different code pages on
different columns within the same table.. so.. Just another symptom
that you need to bite the bullet and move to ADP if you want to use
this functionalty (aka the best performance)

-Aaron