How to convert a string to integer in SQL Server 2008?

Question

I am being challenged by SQL Server. I have this simple query

SELET * 
FROM mytable
WHERE ISNUMERIC(propertyvalue) = 1 
  AND CONVERT(int, CONVERT(decimal(9, 0), ISNULL(propertyvalue, 0 ))) > 0 

I tried to change the conversion line from

CONVERT(decimal(9, 0), ISNULL(propertyvalue, 0))

to

CONVERT(decimal(9, 2), ISNULL(propertyvalue, 0))

or

CAST(ISNULL(propertyvalue, 0) AS numeric)

none of what I am trying is working what so ever. I keep getting this error

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Any ideas on how to solve this problem?

EDITED

The propertyvalue has the type of varchar(255) and it has data like

2
1.5
2.1
String
1String 456

Answer 1

I believe you need to filter out the string data before trying the convert:

 SELECT *
 INTO   #temp
 FROM   mytable
 WHERE  propertyvalue NOT LIKE '%[^0-9]%' 

 SELECT *
 FROM   #temp
 WHERE  CONVERT(INT, CONVERT(DECIMAL(9, 0), ISNULL(propertyvalue, 0))) > 0

 DROP TABLE #temp