numeric * varchar - SQL Server Forums

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

numeric * varchar

Author

Topic

paritosh
Starting Member

42 Posts

Posted - 2011-09-02 : 05:35:31

if we multiply numeric data type from varchar data type then what is the answer

please give one answer

@a int
set @a =5
@b varchar(5)

@b= '10'

select @a*@b

what is return type

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 05:37:18

it will be numeric provided that varchar contains only numeric data. it undergoes implicit conversion on the background. However, if it has non numeric data also stored it will throw an error.

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

paritosh
Starting Member

42 Posts

Posted - 2011-09-02 : 05:57:41

please give one example

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 06:17:51

see:-


declare @test table
(
col1 varchar(10)
)

insert into @test
select '1000' union all
select '10' union all
select '320' union all
select '100' union all
select '12'

select col1  * 4
from @test

declare @test2 table
(
col1 varchar(10)
)

insert into @test2
select '1000' union all
select '10' union all
select 'pc' union all
select '100' union all
select '12r4'

select col1  * 4
from @test2

output
--------------------------

-----------
4000
40
1280
400
48


-----------
4000
40


Msg 245, Level 16, State 1, Line 29
Conversion failed when converting the varchar value 'pc' to data type int.

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

Kristen
Test

22859 Posts

Posted - 2011-09-02 : 07:38:19

You would be better to use EXPLICIT conversion - if for no other reason than the next person who looks at your code (which might be you even!) will know that you MEANT to mix up Char and Numeric data in this way.

Otherwise it just looks like sloppy code at best, or a bug at worst.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 07:55:52

if you so sure that it has only numeric data then make it some numeric type itself.

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

Kristen
Test

22859 Posts

Posted - 2011-09-02 : 07:58:17

That's a better idea

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-09-05 : 05:41:20

quote:
Originally posted by paritosh

if we multiply numeric data type from varchar data type then what is the answer

please give one answer

@a int
set @a =5
@b varchar(5)

@b= '10'

select @a*@b

what is return type

It depends on the implicit convertion. The easiest way is to move data to temp table and view it's definition

select @a*@b as data into #t

GO

exec tempdb..sp_help '#t'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources