How to convert a varchar into numeric field and get the data on condition

Question

How to convert a varchar into numeric field and get the data on condition

Sree-258588

Mr or Mrs. 500

Points: 533
More actions
September 6, 2010 at 7:49 am

#223464

I've a table with varchar column which has got lab test results. I has varied data like numbers (12,23,6666..),float values (0.3,78.77..),values like -> "-","<1.0".
Now my task is to get the data whose test results are > 60 for a particular test.
But when I do this...its giving error that "Arithmetic overflow error converting varchar to data type numeric".
Hence I tried to use ISnumeric....But in case of "-" isnumeric is return true... Thus this failed.
Please help me...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Provide sample data as per this link : http://www.sqlservercentral.com/articles/Best+Practices/61537/

You will receive tested , optimized, beautified code..there are coding beasts 😀 that reside in this site which will provide u with the best possible code.. so please follow the instructions as per that above link..

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Will this assist you?

CREATE TABLE #T(Something VARCHAR(20))

INSERT INTO #T

SELECT '12' UNION ALL

SELECT '.3' UNION ALL

SELECT '-' UNION ALL

SELECT ',1.0' UNION ALL

SELECT '>60' UNION ALL

SELECT '> 60' UNION ALL

SELECT '12,> 60,xyz' UNION ALL

SELECT Something FROM #T WHERE Something LIKE '%>60%' OR Something LIKE '%>%60%'

Result:

>60

> 60

12,> 60,xyz

Editted to expand possible combinations of data

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 3

You could try this:

WHERE [column] NOT LIKE '%[^0-9.]%' AND CAST([column] AS decimal(38, 10)) > 60

If plus signs can be in the data, change to: '%[^+0-9.]%'

Scott Pletcher, SQL Server MVP 2008-2010