[thelist] help with query Part I
Matt Warden
mwarden at gmail.com
Mon Oct 31 23:44:37 CST 2005
-----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-----
More information about the thelist
mailing list