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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Remove decimal with an update statement

Author  Topic 

ppatel112
Starting Member

35 Posts

Posted - 2015-04-26 : 21:12:59
Hi Peeps,

i have a text column which has decimal places.

50000.00
500.00

i can use select cont_franchisefee2,CAST(cont_franchisefee2 AS INT) to see the value without decimal places but i need to updated those values to remove .00000 from the value.

any advise on how to achieve this?

thanks,
parth

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-26 : 22:15:19
update table
set cont_franchisefee2 = cast(cAST(cont_franchisefee2 AS INT) as text)
where cast(cont_franchisefee2 as decimal(18,8)) % 1 = 0.0

adjust the decimal(n,m) to values appropriate for your data
Go to Top of Page

ppatel112
Starting Member

35 Posts

Posted - 2015-04-27 : 00:11:05
HI there it doesnt work - comes up with:
Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to text is not allowed.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-27 : 06:52:55
cast(cAST(cont_franchisefee2 AS INT) as varchar(20))
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-27 : 09:05:14
Thanks K! Also,

1. Is your column really of type text?
2. If so, why? (you're storing numeric data!)
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-27 : 13:53:06
I suggest leaving the column as character because conversions to numeric and back to char are more overhead and could cause errors:

UPDATE table_name
SET cont_franchisefee2 = LEFT(cont_franchisefee2, CHARINDEX('.', cont_franchisefee2 - 1)
WHERE cont_franchisefee2 LIKE '%.%'

Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-27 : 14:29:01
Perhaps add??, for some small!! extra safety:

WHERE cont_franchisefee2 LIKE '%.%'
AND cont_franchisefee2 NOT LIKE '%.%.%'
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-27 : 15:33:41
also


... AND cont_franchisefee2 NOT LIKE '%^[0-9.]%'


hopefully there's no scientific formatted numbers....
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-27 : 16:11:10
If you want to change only rows where the decimal values is .00[00] values, then do this:

UPDATE table_name
SET cont_franchisefee2 = LEFT(cont_franchisefee2, CHARINDEX('.', cont_franchisefee2 - 1)
WHERE cont_franchisefee2 LIKE '%.00%'
OR cont_franchisefee2 LIKE '%.0000%'

Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-27 : 16:16:30
Of course that would change

"version 1.2.00.4" to "version 1"

probably not what the OP wants.
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-28 : 03:28:16
What does a version number have to do with a franchise fee??
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-28 : 08:56:36
Nothing, but the column is datatype text. Anything is possible.
Go to Top of Page
   

- Advertisement -