check varchar column for values that are only numeric - HOW

  • 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

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

    SELECT ISNUMERIC('$123.5')

    SELECT CAST('$123.5' as DECIMAL(19,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
  • 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.

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

    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

    AND ABC.observ_value NOT LIKE '%.%.%'

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

    ELSE 0

    END > 8.0

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher (10/14/2014)


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

    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

    AND ABC.observ_value NOT LIKE '%.%.%'

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

    ELSE 0

    END > 8.0

    +1

    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

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

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