Category: SQL Server

28 Mar 2018 12:45

Joined:	14/09/2006
Level:	Silver
Points:	161

Points:	2

Conversion varchar to int

Hi,
I have a table column varchar type, values are such 1,2.5,6.7,1.7,2.8,1.0 etc..
I want to update this round the number which are having with ".".
1.0 should be updated as 1 and 6.7 should be updated as 7 (this column is varchar data type)

#769557

03 Apr 2018 14:28

Joined:	13/01/2015
Level:	Silver
Points:	230

Points:	2

Hi Sankar,

You can cast your values to float and then to int.See the example below:

declare @x varchar(10)='96.907'

select cast(cast(@x as float) as int) IntValue

Thanks!
Anjali Bansal

~Give your best and lead the world

#769560

03 Apr 2018 14:57

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	2

Hi

you can use this piece of query working Good then future Query or issue post again




declare @d nvarchar(20)='1.0'
select ROUND(@d,2)
--your output
--1

declare @j nvarchar(20)='6.7'
select ROUND(@j,0)

--your output 
--7

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

#769722

14 Aug 2018 10:47

Joined:	28/09/2005
Level:	Gold
Points:	637

Points:	2

Write stored proc with loop and read all the table values and apply below logic before updating values into table

declare @column1 nvarchar(max)
select @column1 = '6.7'
declare @res decimal(2,0)
SELECT @res=round(CAST(@column1 AS decimal(2,0)),0)
select @res

Thanks!
B.Ramana Reddy

Conversion varchar to int

About dotnetspider.com

Quick Links

General

Services