Delete commas and decimal place

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

Delete commas and decimal place

Author

Topic

Roseli
Starting Member

4 Posts

Posted - 2007-12-19 : 16:21:19

How can I convert a field with commas, cents and decimal to a numeric without commas and decimals and cents...?

Sample nvarchar values to desired output (integer whole value, truncated or rounded doesn't matter):
0.00 to 0
14,000.04 to 14000
139,234.31 to 139234
14,017,300.00 to 14017300

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 16:41:18

SELECT ROUND(REPLACE(Col1, ',', ''), 0, 1)
FROM Table1

Change the "1" to "0" if you want ROUNDING instead of TRUNCATING.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 01:53:42

And you may need to convert the result to int or bigint to round decimal part totally

Also it is time to say "use proper datatype to store data"

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources