check varchar column for values that are only numeric - HOW

Question

Post reply

check varchar column for values that are only numeric - HOW

mw_sql_developer

SSCoach

Points: 19445
More actions
October 14, 2014 at 10:47 am

#311810

One column in the table is varchar(10).
This has values like '6.7' , '3' , 'See Comment' ,'00.45'
How do I add a filter in my where clause that will only give me the numeric ones
I get the error message "Error converting data type varchar to numeric..."
When I run the following query
Select top 100 ABC.observ_value, * from
(
SELECT P.last_name, p.first_name, P.date_of_birth, l.test_desc,
CONVERT(VARCHAR(28), r.coll_date_time, 112) collection_dt ,x.result_desc,
(x.observ_value)
FROM lab_nor l
inner join lab_results_obr_p r on (l.order_num = r.ngn_order_num )
inner join lab_results_obx x on (r.unique_obr_num = x.unique_obr_num )
inner join patient_encounter pe on ( pe.enc_id = l.enc_id )
inner join person p on ( p.person_id = pe.person_id )
WHERE
-- x.result_desc like '%HDL%'
--AND r.person_id = 'replace with person id '
--AND
l.test_status = 'Final'
AND l.delete_ind = 'N'
---AND r.enterprise_id = '000002'
AND r.practice_id = '0038'
AND CONVERT(VARCHAR(28), r.coll_date_time, 112) <= '20141020'
AND
x.result_desc like 'H%'
and
x.result_desc like '%A1c%'
and
ISNUMERIC(x.observ_value)=1
)ABC
WHERE
CAST( ABC.observ_value as decimal(19,2) ) > 8.0

Viewing 6 posts - 1 through 6 (of 6 total)

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

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 1

ISNUMERIC doesn't necessarily mean it will convert successfully to a decimal.

SELECT ISNUMERIC('$123.5')

SELECT CAST('$123.5' as DECIMAL(19,2))

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

This is one of the reasons to use the appropriate data types for your columns. This validation might not work if you have 2 or more points in your string.

I changed your dates manipulation.

SELECT P.last_name

,p.first_name

,P.date_of_birth

,l.test_desc

,CONVERT(CHAR(8), r.coll_date_time, 112) collection_dt --use only the needed length

,x.result_desc

,x.observ_value

FROM lab_nor l

INNER JOIN lab_results_obr_p r ON (l.order_num = r.ngn_order_num)

INNER JOIN lab_results_obx x ON (r.unique_obr_num = x.unique_obr_num)

INNER JOIN patient_encounter pe ON (pe.enc_id = l.enc_id)

INNER JOIN person p ON (p.person_id = pe.person_id)

WHERE l.test_status = 'Final'

AND l.delete_ind = 'N'

AND r.practice_id = '0038'

AND r.coll_date_time < '20141021' --This will be converted to a date

AND x.result_desc LIKE 'H%'

AND x.result_desc LIKE '%A1c%'

AND CASE

WHEN ABC.observ_value NOT LIKE '%[^0-9.]%' --allow only numbers and decimal point

--EDIT: Added aditional validation. Note that all this will affect performance.

AND LEN(ABC.observ_value) - LEN(REPLACE( ABC.observ_value, '.', '')) < 2

THEN CAST(ABC.observ_value AS DECIMAL(19, 2))

ELSE 0

END > 8.0

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

As ZZartin has said, ISNUMERIC() = 1 doesn't guarantee that the value can be CAST to a NUMERIC type. You'd think that would be how it would work, but it isn't. I'd explain further but Jeff Moden has already done it in this article, http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/. Give that a read and if it doesn't help you post back here.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ScottPletcher SSC Guru Points: 101200 More actions · Answer 4

I suggest a different method to avoid 2 or more decimal points: