CODEWITHSUNDEEP
sqlsql-servernumerictypeconverter
Jaylen
Jaylen

Reputation: 40289

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

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

Upvotes: 1

Views: 37144

Answers (2)

Dave.Gugg
Dave.Gugg

Reputation: 6771

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

Upvotes: 4

lookslikeanevo
lookslikeanevo

Reputation: 565

i think it was to do with the period, it doesnt know how to convert it.

have you tried float?

CONVERT(float, ISNULL(propertyvalue, 0 ))

Upvotes: -1

Related Questions