April 28, 2009 at 7:43 am
Hi Guys,
This should be easy for some of you - I am looking at SQL Query which will convert succesfully varchar to numeric.
I am using the query below which should do the trick:
Select *
from Table1 a INNER JOIN Table2 b ON Convert(numeric(17,2), b.Col1) = a.Col2
The column b.Col1 is a varchar and a.Col2 is numeric.
When I run this query it gives me an error
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Please correct what I might be doign wrong.
Cheers
Arun
April 28, 2009 at 7:48 am
are you sure that all your varchar values are numerically compatebly?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 7:50 am
this might work, by converting the numeric to varchar instead.
select *
from thangela.NpowerNorthern_v1 a INNER JOIN EPPS.dbo.Customers b ON b.MPANCORE = CAST(a.MPAN1 as VARCHAR)
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 28, 2009 at 12:35 pm
It looks like you have some non numeric data in your column. Maybe this would help:select
*
from
thangela.NpowerNorthern_v1 a
INNER JOIN
(
SELECT
--Columns needed,
CAST( b.MPANCORE AS numeric(17,2) AS MPANCORE
FROM
EPPS.dbo.Customers b
WHERE
b.MPANCORE NOT LIKE '%[^0-9.-]%'
) AS T
ON T.MPANCORE = a.MPAN1 EDIT - I didn't read your post correctly. I though you were doing a calculation. So I changed the code.
April 29, 2009 at 4:38 am
Brilliant both the queries works.
thanks guys!
Keep the good work of sharing knowledge.
April 29, 2009 at 5:09 am
if you are willing to ignore records with non-numeric value in MPANCORE,
try this
select *
from thangela.NpowerNorthern_v1 a
INNER JOIN EPPS.dbo.Customers b ON case when IsNumeric(b.MPANCORE) = 1 then b.MPANCORE else -1 end = a.MPAN1
-- Asuming -1 will not be the valid value for a.MPAN1
April 29, 2009 at 2:20 pm
Atif Sheikh (4/29/2009)
if you are willing to ignore records with non-numeric value in MPANCORE,try this
select *
from thangela.NpowerNorthern_v1 a
INNER JOIN EPPS.dbo.Customers b ON case when IsNumeric(b.MPANCORE) = 1 then b.MPANCORE else -1 end = a.MPAN1
-- Asuming -1 will not be the valid value for a.MPAN1
That might work, but you need to be carfull using SQL's "IS" functions.. DECLARE @Foo VARCHAR(50)
SET @Foo = '1e1'
-- Is numeric
SELECT ISNUMERIC(@Foo)
-- Conversion fails
SELECT CAST(@Foo AS NUMERIC(17,2))
April 29, 2009 at 9:28 pm
Thats right. And thanks for pointing.
Also, '1d1' is a Numeric Value but generate error on conversion. The values are valid from '1d1' - '1d308' (if I am not wrong).
But it will be a highly exceptional case to have one occurence of d or e within the range. But, still, the point is valid.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy