First of all, you should never store formatted data in the database and you usually shouldn't format data in SQL Server. It's much better for a dozen reasons to do the formatting in the GUI.

Also, without knowing the underlying datatype of the data you want to convert, I can only guess that you have the data stored as VARCHAR (again, that's a VERY bad thing to do). Here's how to preserve the formatting you requested on a VARCHAR...

--===== Create a data sample... this is not a part of the solution

DECLARE @SomeFormattedColumn VARCHAR(20)

SET @SomeFormattedColumn = '$4,816,220.65'

--===== Solve the problem

SELECT LEFT(@SomeFormattedColumn, LEN(@SomeFormattedColumn)-3)

Of course, that will work correctly ONLY if all your data has a decimal point and two decimal places. You could use this as a bit of a guarantee...

--===== Create a data sample... this is not a part of the solution

DECLARE @SomeFormattedColumn VARCHAR(20)

SET @SomeFormattedColumn = '$4,816,220.65'

--===== Solve the problem

SELECT LEFT(@SomeFormattedColumn,

LEN(@SomeFormattedColumn)

- ISNULL(LEN(@SomeFormattedColumn)

- NULLIF(CHARINDEX('.',@SomeFormattedColumn),0)+1

,0)

)

The numeric functions in that will be faster than doing multiple CONVERTs or CASTS or even using REVERSE.

... and now you see why you shouldn't store or create formatted data in SQL Server. 😉