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

Dave Gugg has the correct answer below. In your select statement's where clause, because the ISNUMERIC(...) = 1 and the CONVERT(..) > 0 conditions are on the same level they are both evaluated no matter if the value is numeric or not. The non-numeric values must be filtered first. — Eduard Uta
– Eduard Uta, Commented Nov 20, 2014 at 21:22

Dave.Gugg · Accepted Answer · 2014-11-20 21:36:47Z

4

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

edited Nov 20, 2014 at 21:36

answered Nov 20, 2014 at 21:16

Dave.Gugg

6,8013 gold badges26 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Jaylen Over a year ago

It is not working. I tried to do the conversion in the select as well but that is not working. I get Error converting data type varchar to numeric.

Lamak Over a year ago

Just because you are using a CTE, it doesn't mean that the results are filtered first. Also, this doesn't take into account values like '1e1', where ISNUMERIC returns 1

Dave.Gugg Over a year ago

Edited to correct Lamak's first objection. As to the ISNUMERIC "bug", I don't have a good way to correct. Suggestions?

Eduard Uta Over a year ago

The real issue is that ISNUMERIC is "tricked" by values like '1e1'. sqlservercentral.com/Forums/Topic243646-8-1.aspx

Eduard Uta Over a year ago

another idea is to use TRY_PARSE(propertyvalue as int). And the condition would be WHERE TRY_PARSE(propertyvalue as int) IS NOT NULL.

|

lookslikeanevo · Accepted Answer · 2014-11-20 21:28:24Z

-1

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 ))

edited Nov 20, 2014 at 21:28

answered Nov 20, 2014 at 21:15

lookslikeanevo

5731 gold badge5 silver badges15 bronze badges

1 Comment

lookslikeanevo Over a year ago

works for me, if you copied your query directly, you have selet and not select

Collectives™ on Stack Overflow

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

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related