Remove decimal with an update statement

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

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.

Kristen
Test

22859 Posts

Posted - 2015-04-27 : 06:52:55

cast(cAST(cont_franchisefee2 AS INT) as varchar(20))

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!)

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 '%.%'

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 '%.%.%'

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....

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%'

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.

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??

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.