Converting Data

Question

Post reply

Converting Data

RTaylor2208

SSChampion

Points: 13296
More actions
April 20, 2009 at 7:13 am

#208644

Hi,

This is probably a simple one for someone with more experience than me but it's got me stumped.

We have a table which holds values of different types but just stored as varchars i.e.

CREATE TABLE tbl_values2 (

value_id int IDENTITY (1, 1) NOT NULL ,

value_label varchar(255) NOT NULL,

value varchar (255) NOT NULL)

INSERT INTO tbl_values2 (value_label, value)

SELECT 'HBA1c','7'

UNION SELECT 'HBA1c Date', '01-01-2009 00:00:00.000'

UNION SELECT 'HBA1c','ABC'

UNION SELECT 'HBA1c Date', '10-01-2009 00:00:00.000'

The problem we have is that the app that is used to populate the data lets you enter any free text into the value as it is not validated. (I know poor design on both the app and db).

What we want to do for example is get a list of all HBA1c's and display them as a decimal value. But as in this example one is ABC and not a number it fails to convert it to an decimal thus the query fails.

SELECT CONVERT(decimal(5,2),value) from tbl_values

WHERE value_label = 'HBA1c'

Which gives the error:

Server: Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

Is there anyway I can get it to just return the successfully converted row and ignore the one that fails?

Cheers

R

MCITP SQL 2005, MCSA SQL 2012

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

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

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

While not perfect try using the IsNumeric Function, check out the BOL Entry for it. So your query could be this:

SELECT



    CONVERT(decimal(5,2),value)

from

    tbl_values

WHERE

    value_label = 'HBA1c' And

IsNumeric(value) = 1

You may still get some errors which is why I mention reading the BOL Entry for IsNumeric.

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

Sergiy SSC Guru Points: 110192 More actions · Answer 2

Sergiy

SSC Guru

Points: 110192

April 20, 2009 at 3:53 pm

#981082

What we want to do for example is get a list of all HBA1c's and display them as a decimal value. But as in this example one is ABC and not a number it fails to convert it to an decimal thus the query fails.

Which way you intend to display 'ABC' as a decimal value?

_____________
Code for TallyGenerator