0

this is my first post and i apologise if i dont quite ask correctly.

my result column looks like this

Results_

4.5
3.2
1.1
17.1
30.2
.{not done}
.Not competed
18.0

what i would like to do is retrieve the entries that are above 3.6

is this possible?

I have tried convert/cast as float/int/nvarchar(50)

Yuck
  • 49,664
  • 13
  • 105
  • 135
grahamie
  • 301
  • 2
  • 6
  • 15
  • 3
    Which DBMS are we on? MySQL? PostgreSQL? Sqlite? – Joseph Victor Zammit Oct 02 '12 at 15:20
  • this is why you store numbers as "numbers" and not as strings. it would be trivial if the proper data type was used in the original design. – KM. Oct 02 '12 at 15:36
  • @Josvic Zammit, OP says `I have tried convert/cast as float/int/nvarchar(50)`, so I'd say TSQL and most likely SQL Server and not Sybase. – KM. Oct 02 '12 at 15:39
  • using SQL - i am not the administrator jsut a self taught (stackflow taught) user. the IT guys are not very helpful – grahamie Oct 02 '12 at 15:50
  • To encourage people to help you, your question and responses should be as explicit as possible. Anyone reading your question has little context to go on. When you say you are using SQL, that is not explicit. Do you mean SQL server (a version helps too), or are you unclear about the difference between the DBMS and the SQL language. The latter probably means you need pointers to some basic tutorials as your answer. The former means you need answers from one set of experts (i.e. SQL server users) and not another (say Oracle users). – Stuart R. Jefferys Oct 02 '12 at 17:55
  • thank you Stuart. as stated in my original statement I apologise for not asking correctly. the answer was provided super quick and i am thankful. I will try better next time. – grahamie Oct 09 '12 at 10:30

2 Answers2

0

In sql server you can do this

;with cte as (
Select * from table where isnumeric(result_col) = 1)

select * from cte where cast(result_col as decimal(18,1)) > 3.6 
order by result_col

Also check this post by richard where he posted a custom function to check if a value is numeric or not, isnumeric will fail for '.' and '-' and looks like you have rows with '.'

https://stackoverflow.com/a/12674851/125551

Community
  • 1
  • 1
rs.
  • 26,707
  • 12
  • 68
  • 90
0

SQL Server does not guarantee the ordering of results, except for a case statement without aggregation. To do this in SQL Server and guarantee that you don't get an error:

select *
from t
where (case when isnumeric(col) = 1 then cast(col as float) end) > 3.6

In other databases, you would use a regular expression or like pattern to match numbers:

where (case when col like '[0-9]*' or col like '[0-9]*.[0-9]*' then cast(col as float) end) > 3.6

I admit that the case statement in the WHERE clause is unpleasant. But, to prevent errors, there is little alternative since the sequencing of operations is important.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786