April 20, 2009 at 7:13 am
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
April 20, 2009 at 7:35 am
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
April 20, 2009 at 3:53 pm
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
April 21, 2009 at 3:44 am
Jack, thanks for this will give it a try. Is there a ISDATE as we have the same problem for dates as well?
MCITP SQL 2005, MCSA SQL 2012
April 21, 2009 at 5:53 am
Yes there is an IsDate function as well.
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
April 21, 2009 at 6:07 am
Cracked it, thanks for the help.
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