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

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

Answer 1

You can try

SELECT CASE WHEN ISNUMERIC(nvCol) THEN CAST(nvCol as DECIMAL(15,0))
    ELSE 0.0 END as DecData
FROM YourTable

ISNUMERIC may be too general for your use (it allows some really strange "numbers"), if so then you may need to add your own conditions to filter on. You will also have to get the sizing of DECIMAL right. Finally, you may prefer to have the check in the WHERE clause to filter out these rows instead of defaulting them to 0.0.

Answer 2

use database go

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