How do I read inconsistent nvarchar data as decimal data

Question

For SSRS 2005 report how do i read inconsistent nvarchar data values from database as consistent numeric decimal data for plotting a line chart?

the nvarchar column has lab readings to be plotted on a graph as nvarchar(15) datatype.

sample column with inconsistent data as shown

columnvalues 00123 102 (NULL) 333 456 N/R No Result 567 589

Answer 1

My boss helped me with this query. You can add more clauses at the end of the following query depending on the inconsistencies in your data.

use database go select convert(decimal (10,2), ltrim(rtrim(columnname))) from table where isnumeric(columnname) = 1 and ltrim(rtrim(columnname)) <> '.'

Answer 2

Presuming your table is LabData and your numeric column is named LabReading:

SELECT
    LabReadingValue =
     CASE 
      WHEN ISNUMERIC(LabReading)=1
      THEN CAST(LabReading AS DECIMAL(15,4))
      ELSE NULL 
     END
FROM
    LabData

If you want to filter out the non-numeric values, you can add:

WHERE ISNUMERIC(LabReading)=1