September 15, 2013 at 9:40 pm
Hi Professionals
I have a varchar column in my table with pricing information in it that has up to 4 decimal places
how do i update the column to make it only 2 decimal places
I know I could substring through the column and look for the decimal piont + 2 places but I dont know how to put this into an update to update the specific column in question
any ideas
thanks in advance
September 16, 2013 at 2:01 am
UPDATE t
SET col = CAST(col AS NUMERIC(19, 2))
FROM table_price t
?
September 16, 2013 at 5:46 am
Provided that you really do have numeric data in all your rows, the CAST would be a better approach that string parsing to find a decimal. The real question is why you're storing numeric data in a varchar column in the first place.
September 16, 2013 at 8:29 am
Ed Wagner (9/16/2013)[hrThe real question is why you're storing numeric data in a varchar column in the first place.
+1000
Do yourself a massive favor and change your datatype to be appropriate to the data stored in the column.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2013 at 5:38 am
Sean Lange (9/16/2013)
Ed Wagner (9/16/2013)[hrThe real question is why you're storing numeric data in a varchar column in the first place.
+1000
Do yourself a massive favor and change your datatype to be appropriate to the data stored in the column.
Amen. This is an altruism that I constantly try to get other people to do. Once a system is in production, it can be difficult to change data types, so designing it correctly up front is paramount.
September 17, 2013 at 10:39 am
UPDATE dbo.tablename
SET col = LEFT(col, CHARINDEX('.', col) + 2)
WHERE col LIKE '%.___%'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply