CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jon
Jon

Reputation: 40032

SQL - Calculating if an alphanumeric value exists in an alphanumeric range

I have a varchar column in a database and a requirement has come in so a user can enter a range to/from eg/ABC001 to ABC100

I have the following query but feel it might not be strict enough to work out if any values within that range exist.

SELECT count(*) FROM MyTable where MyColumn between 'ABC001' and 'ABC005'

I have a feeling an order by should be used or is there a better way to calculate the existence of values within a alphanumeric range

Upvotes: 0

Views: 597

Answers (3)

KM.
KM.

Reputation: 103597

OP said:

or is there a better way to calculate the existence of values within a alphanumeric range

The best way would be:

SELECT count(*) FROM MyTable where MyColumn>='ABC001' and MyColumn<='ABC005'

I find most people can't remember if BETWEEN includes or excludes the "end points". By just always using >= and/or > and/or <= and/or < you have more clarity and flexibility.

Any ORDER BY would be applied to the resulting set of rows that meet the WHERE condition, and has nothing to do with the WHERE filtering. You can use it if you want the final result set in a particular order, but it will have no effect on which rows are included in the results.

Upvotes: 0

Damian Leszczyński - Vash
Damian Leszczyński - Vash

Reputation: 30865

If you want to boost that operation you can create a index on it.

Order by operation is done at the end of query execution, so the data will be retrived in the same way.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166406

No orderby is required. That should be perfrect.

Upvotes: 1

Related Questions