Query For Whole Numbers 1

[OrWolf]

I'm attempting to query against a table to pull only records where the number in a particular field doesn't have a decimal.

Data
ProjectID OutlineNo
1234 1
1234 2
1234 2.1

So in the case above I don't want the record with an outline number of 2.1. The following is the best I've come up with but it doesn't seem very efficient.

SELECT * FROM Projects WHERE OutlineNo NOT LIKE '%.%'

Any other ideas would be appreciated. Thank you.

 

[gmmastros]

What is the data type for the OutlineNo field?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[OrWolf]

nvarchar

 

[gmmastros]

There are lots of ways (including your way which is valid too).

SELECT * FROM Projects WHERE OutlineNo LIKE '[^.]'
SELECT * FROM Projects WHERE ParseName(OutlineNo, 2) Is NULL
SELECT * FROM Projects WHERE CharIndex('.', OutlineNo) = 0

There are other ways too. I suspect that they will all perform the same, so just pick one.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom