Varchar to numeric ???

Question

Post reply

Varchar to numeric ???

sqlnaive

SSCoach

Points: 17437
More actions
September 20, 2013 at 5:18 am

#278209

Recently I was playing with different datatype conversions and saw one strange thing while converting one of the numeric value in varchar datatype to numeric datatype. Here is the code:
declare @varchar varchar(50)
select @varchar = '8E10'
select @varchar-- Returns 0E10
select isnumeric(@varchar)-- Returns 1
select convert(numeric(28,10), @varchar)-- Error converting data type varchar to numeric.
select cast(@varchar as numeric(28,10))-- Error converting data type varchar to numeric.
go
As you can see, '8E10' return 1 as a result of IsNumeric function but while using convert or cast, it gives error. Probably it's meant to but can anyone put light on this ?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Pearce SSCoach Points: 15999 More actions · Answer 1

The ISNUMERIC function tells you if you have a valid int, numeric or float. 8E10 is a valid float.

declare @varchar varchar(50)

select @varchar = '8E10'

select @varchar

select isnumeric(@varchar)

select convert(float, @varchar)

go

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

psingla Hall of Fame Points: 3840 More actions · Answer 2

Sean Pearce (9/20/2013)
The ISNUMERIC function tells you if you have a valid int, numeric or float. 8E10 is a valid float.
declare @varchar varchar(50)
select @varchar = '8E10'
select @varchar
select isnumeric(@varchar)
select convert(float, @varchar)
go

thanks Sean

It could be real or float

but still wondering why it is considered as real or float only

i guess it is something to do with exact numeric and approximate numeric

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

sqlnaive SSCoach Points: 17437 More actions · Answer 3

Thanks Seane. So in order to convert it to numeric/decimal, two conversions have to be done like this:

select convert(numeric(28,8), convert(float, @varchar))

float and Numeric are both number data types so why we can't convert the varchar directly to numeric ? The difference between the two being "exact" and "size limit", what's the factor which doesn't make it in one go ?

How does oracle deal with this (directly convert varchar to numeric ?) ?

Sean Pearce SSCoach Points: 15999 More actions · Answer 4

It also works with the STR function

SELECT CONVERT(NUMERIC(28, 10), STR('8E10', 28, 10));

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

sqlnaive SSCoach Points: 17437 More actions · Answer 5

My question remains the same:

float and Numeric are both number data types so why we can't convert the varchar directly to numeric ? The difference between the two being "exact" and "size limit", what's the factor which doesn't make it in one go ?

Sean Pearce SSCoach Points: 15999 More actions · Answer 6

I was avoiding the "why" because I don't have an answer.

This works

DECLARE @Numeric NUMERIC(28, 10) = 8E10;

SELECT @Numeric;

This doesn't

DECLARE @Numeric NUMERIC(28, 10) = '8E10';

SELECT @Numeric;

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

sqlnaive SSCoach Points: 17437 More actions · Answer 7

🙂 No worries Sean. but thanks about the STR functionality. Was playing with it and had fun to know about some things. 🙂

Adding to your command statements, these two works perfectly:

DECLARE @float Float = 8E10;

SELECT @float;

GO

DECLARE @float Float = '8E10';

SELECT @float;

GO

sqlnaive SSCoach Points: 17437 More actions · Answer 8

Can anyone please put some light into it ?

float and Numeric are both number data types so why we can't convert the varchar directly to numeric ? The difference between the two being "exact" and "size limit", what's the factor which doesn't make it in one go ?

I tried checking number of references but couldn't get any. 🙁

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 9

sqlnaive (9/20/2013)
🙂 No worries Sean. but thanks about the STR functionality. Was playing with it and had fun to know about some things. 🙂

Be a bit leary of the STR() function. Please see the following article for why.

http://www.sqlservercentral.com/articles/T-SQL/71565/

As to the reason why you can't do the direct conversion that you speak of, the answer is both terrible and terribly simple... MS simply doesn't support it meaning that they didn't write code to do it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlnaive SSCoach Points: 17437 More actions · Answer 10

Thanks Jeff. Nice article.

I came across this function just two days back during some conversion issues. I found some surprisingly different results and thus started playing with it (for fun). Possibly time to have some fun question in QOTD based on it's functioning. 🙂

though I must say, it's very dangerous to use this function (as your article suggests as well).