varchar to decimal using convert/cast? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

varchar to decimal using convert/cast?

Author

Topic

Shrews14
Starting Member

37 Posts

Posted - 2009-03-31 : 09:37:53

Hello all,

I wrote a SPROC a while back where i converted a varchar from a table into a decimal and then used this in a calculation with other decimal data as all data in this field is either a NULL or numerical.

The varchar is held in a database table which i can not change the datatype. The data in moved into numerous temp tables inthe sproc before needing convertion/casting. Now i seem to remember casting/converting somewhere in the insert into the temp table!!

I need to do this again but can't quite remember how! I do rememeber it was annoyed but found a fairly easy work around.

NB my previous work was for my old company which made me redundant so i can't get hold of it.

anybody help!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-31 : 09:40:37

to convert to decimal...

select convert(decimal(10,2),<urcolumn>) from <urtable>

Shrews14
Starting Member

37 Posts

Posted - 2009-03-31 : 10:04:47

quote:
Originally posted by vijayisonly

to convert to decimal...

select convert(decimal(10,2),<urcolumn>) from <urtable>

Thanks Vijay

I presume you mean in my insert into the temp table i can just use a convert. I sure i tried this but will get back may have just used cast.

thanks for your quick reply!

Shrews14
Starting Member

37 Posts

Posted - 2009-03-31 : 10:10:03

INSERT INTO ...
SELECT .....
convert(decimal(19,10), dbo.dt_result.method_detection_limit),
convert(decimal(19,10), dbo.dt_result.reporting_detection_limit),
convert(decimal(19,10), dbo.dt_result.quantitation_limit),
.....

Msg 8114, Level 16, State 5, Procedure Chemistry_Exceedence, Line 69
Error converting data type varchar to numeric.

Not working anybody know what i can try?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-31 : 10:20:38

what are the possible values of method_detection_limit, and the other 2 fields? Do they have ONLY numeric values?

Shrews14
Starting Member

37 Posts

Posted - 2009-03-31 : 10:25:36

Nulls or Numerical values

Theses are the only things in the database (I have checked just checked) and will be the only thing that will be imported. I would like to mod the the database table but sadly cannot!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-31 : 10:34:58

When you say nulls...is it actually NULL or just an empty string?

the convert should work on NULLs but will fail on the empty string.

Can you try this once...on any one column first

select convert(decimal(19,10),NULLIF( dbo.dt_result.method_detection_limit,'')) from <URTABLE>

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-31 : 10:42:57

quote:
Originally posted by Shrews14

....
NB my previous work was for my old company which made me redundant so i can't get hold of it.

you can periodically zip your work and email it to your personal email account. It's common to have to leave building immediately after redundancy.

Shrews14
Starting Member

37 Posts

Posted - 2009-03-31 : 10:45:50

quote:
Originally posted by vijayisonly

When you say nulls...is it actually NULL or just an empty string?

the convert should work on NULLs but will fail on the empty string.

Can you try this once...on any one column first

select convert(decimal(19,10),NULLIF( dbo.dt_result.method_detection_limit,'')) from <URTABLE>

actual NULLs, i presume from what you are saying that what your saying is it should work! Is a trim worth while as there could be spaces after the 'numerical' varchar string?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-31 : 10:50:40

It doesn't matter..it should work either way..

declare @t table (col1 varchar(30))
insert @t
select ' 30 ' union all
select null union all
select '40.00'

select convert(decimal(10,2),col1) from @t

(3 row(s) affected)

---------------------------------------
30.00
NULL
40.00

Shrews14
Starting Member

37 Posts

Posted - 2009-03-31 : 10:57:25

quote:
Originally posted by darkdusky

quote:
Originally posted by Shrews14

....
NB my previous work was for my old company which made me redundant so i can't get hold of it.

you can periodically zip your work and email it to your personal email account. It's common to have to leave building immediately after redundancy.

I know, wish i had taken it home on a regular basis. Whats done is done :-). One of the best things that happened to me as i mangaed to take a number of clients with me!!!!

Shrews14
Starting Member

37 Posts

Posted - 2009-03-31 : 11:01:49

quote:
Originally posted by vijayisonly

It doesn't matter..it should work either way..

declare @t table (col1 varchar(30))
insert @t
select ' 30 ' union all
select null union all
select '40.00'

select convert(decimal(10,2),col1) from @t

(3 row(s) affected)

---------------------------------------
30.00
NULL
40.00

the code above worked fine. bloody thing :) will have to double check the data as all things are pointing to that!!

Thanks again mister violating yak guru man (Vijay)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-31 : 11:04:29

np...you could run a ISNUMERIC check on your columns to see if there are any alphanumeric data sitting there.

SELECT * from <urtable> where ISNUMERIC(dbo.dt_result.method_detection_limit) <> 1 OR ...

Shrews14
Starting Member

37 Posts

Posted - 2009-03-31 : 11:50:58

quote:
Originally posted by vijayisonly

np...you could run a ISNUMERIC check on your columns to see if there are any alphanumeric data sitting there.

SELECT * from <urtable> where ISNUMERIC(dbo.dt_result.method_detection_limit) <> 1 OR ...

SELECT method_detection_limit from dt_result where ISNUMERIC(dbo.dt_result.method_detection_limit) <> 1 group by method_detection_limit

Query runs correctly
Brings back one NULL row. I'm confused but will figure it out!!

Shrews14
Starting Member

37 Posts

Posted - 2009-04-01 : 11:06:38

Still only numbers and nulls in field yet won't convert anybody know what i should be looking for?

when i run:

SELECT reporting_detection_limit from dt_result group by reporting_detection_limit

i get results like '5.00E-06' could this be the problem and how can i get around it?

Shrews14
Starting Member

37 Posts

Posted - 2009-04-01 : 12:59:10

I found this interesting function, shown on the link below and it showed that the following bit's of data weren't numeric

2.4e-005
1e-005
1.7e-005
1,000
1.00E-05

and couldn't use them. My new question how can i use this data ie change to a 'Numerical' so i can convert to decimal

IsNumeric is not reliable. If you have data like 12,345.45, you have problem with that
Read this
http://aspfaq.com/show.asp?id=2390

posted by Madhivanan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-01 : 13:13:36

try this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

Shrews14
Starting Member

37 Posts

Posted - 2009-04-01 : 14:17:01

quote:
Originally posted by visakh16

try this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

Thanks that corectly identifies the not numerics but doesn't convert them into usable numerics!

Ideally i would like a function to convert varchar strings with '1E-05' (for example) into their numerical selves ie 0.000001. Plus i know it's bad data but i have no/little control over that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources