Convert Column from varchar to numeric

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

General SQL Server Forums

New to SQL Server Programming

Convert Column from varchar to numeric

Author

Topic

JustinM
Starting Member

17 Posts

Posted - 2011-12-16 : 12:12:19

I have spent all morning on Google and various forums and have not been able to get this to work. I am trying to get column IL converted from varchar(10) to numeric(7,2). I have tried the following:

select *
into #tmp
from MyTable
truncate table MyTable
alter table MyTable
alter column IL numeric(7,2)
insert into MyTable
select cast(IL as numeric(7,2))
drop table #tmp

-----

UPDATE MyTable
SET IL = CAST (IL AS numeric(7,2))

------

ALTER TABLE MyTable
ALTER COLUMN IL numeric(7,2)

-------

None of these things worked. Any ideas?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-16 : 12:21:03

How does it not work? ARe you getting a conversion error?

ALTER TABLE MyTable
ALTER COLUMN IL numeric(7,2)

Should work just fine.

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-16 : 12:41:39

Unless of course there is non-numeric data in the column

SELECT * FROM myTable
WHERE ISNUMERIC(IL) = 0

What does that return?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 13:21:12

isnumeric() even returns 1 for some non numeric data so for checking strictly numeric data below might be better option

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JustinM
Starting Member

17 Posts

Posted - 2011-12-16 : 13:59:48

Lamprey: I get the following error when I try that

Arithmetic overflow error converting varchar to data type numeric.

X002548 (Brett):

That returns nothing. Just every column header.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-16 : 14:02:16

Well there you go. You could check to see if any of the values are longer than 7 characters for a start. After that, you could try to narrow things down in other ways.

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-16 : 14:10:32

DO THIS

SELECT TOP 20 [IL] FROM myTable ORDER BY [IL] ASC

Ad then

SELECT TOP 20 [IL] FROM myTable ORDER BY [IL] DESC

And post those results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

JustinM
Starting Member

17 Posts

Posted - 2011-12-16 : 14:10:37

so basically if I change my numeric from (7,2) to something like (12,2) it should work? Assuming the longest value is 12?

JustinM
Starting Member

17 Posts

Posted - 2011-12-16 : 14:13:27

Brett:

-0.01
-0.03
-0.76
-10
-100
-100
-100
-100
-100
-100
-100
-100
-100
-100
-100
-100
-100
-100
-100
-100

9999.65
9999.38
9997.45
9997.27
9995.67
9995.65
9995.09
9994.71
9994.68
9993.69
9993.47
9993.31
9991.63
9991.25
9991.18
9991
9990.9
9990.38
9990.15
9990.08

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-16 : 14:16:08

Perhaps

DECLARE @v CHAR(10); SET @v = '12345678.90'
SELECT CONVERT(decimal(7,2),@v)
SELECT CONVERT(decimal(15,2),@v)

What does

SELECT MAX([IL]) FROM Table tell you?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

JustinM
Starting Member

17 Posts

Posted - 2011-12-16 : 14:23:56

--What does
--
--SELECT MAX([IL]) FROM Table tell you?

Just 9999.65 again

JustinM
Starting Member

17 Posts

Posted - 2011-12-16 : 14:27:44

The query

SELECT MAX(len([IL]))
FROM MyTable

brings back 12. Should I try numeric(12,2)?

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-16 : 14:28:08

DECLARE @v CHAR(10); SET @v = '9999.65'
SELECT CONVERT(decimal(7,2),@v)
SELECT CONVERT(decimal(15,2),@v)

Should work..try this

CREATE TABLE #t1(IL decimal(7,2))

CREATE TABLE #t2(IL decimal(15,2))

INSERT INTO #t1 (IL)
SELECT IF FROM myTable

INSERT INTO #t2 (IL)
SELECT IF FROM myTable

Does either work?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

JustinM
Starting Member

17 Posts

Posted - 2011-12-16 : 14:39:17

quote:
Originally posted by X002548

DECLARE @v CHAR(10); SET @v = '9999.65'
SELECT CONVERT(decimal(7,2),@v)
SELECT CONVERT(decimal(15,2),@v)

I don't understand the context of what's going on here. How does this reference IL or MyTable? Do I need to include something else? what does @v mean?

Thanks,

Justin

JustinM
Starting Member

17 Posts

Posted - 2011-12-16 : 14:43:24

quote:
Originally posted by X002548

CREATE TABLE #t1(IL decimal(7,2))

CREATE TABLE #t2(IL decimal(15,2))

INSERT INTO #t1 (IL)
SELECT IF FROM myTable

INSERT INTO #t2 (IL)
SELECT IF FROM myTable

Does either work?

lol, they both err out but with different error messages!

The first one gives me the arithmetic error

"Arithmetic overflow error converting varchar to data type numeric."

The second one gives me the following error:

"Error converting data type varchar to numeric."

Thanks,

Justin

JustinM
Starting Member

17 Posts

Posted - 2011-12-16 : 14:44:13

Oh, and I assumed by SELECT IF FROM myTable you actually meant SELECT IL FROM myTable

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-16 : 14:50:47

my bad...Hard to test with out the DDL and sample data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-16 : 14:51:20

quote:
Originally posted by JustinM

Oh, and I assumed by SELECT IF FROM myTable you actually meant SELECT IL FROM myTable

Yes..did you try that? Did one or the other work?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

JustinM
Starting Member

17 Posts

Posted - 2011-12-16 : 14:53:31

No running with IL was where I came up with those two errors.

JustinM
Starting Member

17 Posts

Posted - 2011-12-16 : 16:57:24

I ended up reimporting the file and changing the format at import. Looks like it worked okay. Not sure what was going on before that, but the fact that you guys were suggesting things similar to what I was doing makes me feel better about the whole process.

Thanks for all your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources