Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert varchar to numeric Expand / Collapse
Author
Message
Posted Tuesday, April 28, 2009 7:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 07, 2011 11:05 AM
Points: 15, Visits: 58
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
[color=Red]Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.[/color]


Please correct what I might be doign wrong.

Cheers
Arun
Post #705817
Posted Tuesday, April 28, 2009 7:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
are you sure that all your varchar values are numerically compatebly?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #705827
Posted Tuesday, April 28, 2009 7:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #705832
Posted Tuesday, April 28, 2009 12:35 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 10:10 AM
Points: 292, Visits: 1,615
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.
Post #706174
Posted Wednesday, April 29, 2009 4:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 07, 2011 11:05 AM
Points: 15, Visits: 58
Brilliant both the queries works.

thanks guys!

Keep the good work of sharing knowledge.
Post #706626
Posted Wednesday, April 29, 2009 5:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:46 AM
Points: 3,241, Visits: 4,987
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



----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #706655
Posted Wednesday, April 29, 2009 2:20 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 10:10 AM
Points: 292, Visits: 1,615
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))

Post #707207
Posted Wednesday, April 29, 2009 9:28 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:46 AM
Points: 3,241, Visits: 4,987
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.


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #707368
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse