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 Varchar to Numeric with decimal

Author  Topic 

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2011-09-09 : 02:01:26
Hi,

I want to convert the below string values to numeric
the problem is since the "." Is there I need to be identified as decimal
the destination column datatype is numeric (12,3)

SaleVolume
------------
6.73317
45
4.09814
1.22385
-57820
23740
10.42039
2.36408
-28048

Select CAST(SaleVolume AS NUMERIC(17,2))
Select CONVERT(NUMERIC(17,2),AmtTwo )
Error converting data type varchar to numeric.

:( I am not able to use the cast ,convert function :(


Thanks for help in advance


THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 02:11:12
"I am not able to use the convert function"

Please clarify why not?

i.e.

SELECT CONVERT(numeric(12,3), SaleVolume)
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 02:13:06
"Error converting data type varchar to numeric."

You probably have non-numeric data in your column.

SELECT SaleVolume, PKColumn1, ...
FROM YourTable
WHERE SaleVolume LIKE '%[^-.0-9]%'
OR IsNumeric(SaleVolume) <> 1
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2011-09-09 : 02:15:34
Since the varchar have "."
seems the sql is not able to find the exact convertion..
i am not sure on this since i am new to sql :(
what clarificaiton are you looking for.. plse help

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 02:19:15
nope . is not a problem so far as you're converting to numeric. see

select convert(numeric(15,2),'2133244441212.2131423414')

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

Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2011-09-09 : 02:20:11
I will not be able to filter or remove any columns. the data from flat file is something like..
6.73317
45
4.09814
1.22385
-57820
and i need them to coverted into numeric...
i tried the reply logic by Kristen, i am getting same error


THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 02:20:28
what does below return?

select * from table where isnumeric(field) = 0 


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

Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 02:20:53
"what clarificaiton are you looking for"

You have since provided it by editing your original post, thank you.

Run my query above to find the data that is not compatible for conversion. It has nothing to do with the "." in the data.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 02:22:26
"i tried the reply logic by Kristen, i am getting same error"

Post the exact query you used please
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2011-09-09 : 02:23:11
select * from table where isnumeric(field) = 0

returns

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


THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-09 : 02:26:46
may be there is some other issue you are having as from VARCHAR converting to NUmeric is possible.
DECLARE @Value VARCHAR(10)
SET @value=45

SELECT CAST(45 as NUMERIC(12,3))

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2011-09-09 : 02:34:03
i dont know , its not working :(
what do u want from the data source column ?

select convert(numeric(15,2),'2133244441212.2131423414')
is working fine for me.. but when i use my column as input, it is giving same error


THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 02:34:38
"elect * from table where isnumeric(field) = 0

returns

Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.
"

Well I wouldn't have believed that was possible! But I would like to know what value will cause IsNumeric() function to raise error please, when you find it.

Try just

SELECT SaleVolume, PKColumn1, ...
FROM YourTable
WHERE SaleVolume LIKE '%[^-.0-9]%'
AND SaleVolume IS NOT NULL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 02:35:00
quote:
Originally posted by shanmugaraj

select * from table where isnumeric(field) = 0

returns

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


THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com


what about this?

select * from table where not field like '%[^0-9]%'

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

Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 02:35:22
If that fails please post your EXACT query.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 02:48:55
quote:
Originally posted by visakh16

quote:
Originally posted by shanmugaraj

select * from table where isnumeric(field) = 0

returns

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


THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com


what about this?

select * from table where field like '%[^0-9]%'

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





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

Go to Top of Page
   

- Advertisement -