[thelist] help with query Part I

[Matt Warden]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Casey wrote:
>>: OK, the db in question had a column called mls
>>: containing an id.  All id's in this column start with
>>: a 0... some are 07xxxx, some are 06xx, etc.  There
>>: is no set number of total digits.  I need to
>>: select only the rows where mls
>>: starts with 06.
>>:
>>: How would such a where clause be formatted?
>>
>>Since you don't tell us what DBMS you are using, all I can suggest is that
>>you go and look in the manual for whatever DBMS you are using, and look up
>>the string formatting functions you have available to you.
> 
> 
> Details... details....
> 
> MS SQL classic ASP.  I guess I thought that this was a simple enough query 
> question that it didn't matter.
> 
> Would --  WHERE mls LIKE '06%'  -- do the job?

It would, but it is not best.

If your DBMS doesn't insist on treating the string as an integer, you
could probably do this:

... WHERE mls >= '06' AND mls < '07'

That way it could use an index, if you create(d) one.

Another option would be:

... WHERE substring(mls, 0, 2) = '06'

but:
(1) the exact syntax may depend on your DBMS (and I don't know what it
is in MS SQL)
(2) will still probably result in a full table scan, but *might* (BIG
'might') be marginally faster

Your best bet is my first suggestion, if your DBMS will treat the string
as a string and not an integer.

- --
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDZwDErI3LObhzHRMRAn6lAKC2z+M6sG6zKuMRDxB4JKs5Fvm7eACfRa6a
tuL/p8jBW0jOZF8aTBV2QHE=
=pRDV
-----END PGP SIGNATURE-----