SQL Server Portal

I had come across this problem (how to convert varchar to currency) a couple of years ago and fixed this problem. Recently I had a chance to revisit my solutions and I discovered that we can re-script this solution much more efficiently than I did earlier, using FORMAT & TRY_CONVERT (new functions shipped in SQL Server 2012).

Note : It is recommended to do this formatting from the front end of the application.

Let me create a sample to demonstrate the solution :

SAMPLE:

USE [tempdb]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
CREATE TABLE [dbo].[tbl_sample]
(
[Col_ID] [int] NULL,
[Col_varchar] [varchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (1, N'1000.00')
GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (2, N'-10.21')
GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (3, N'1234.5678')
GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (4, N'123.45')
GO

SOLUTION:
In this solution, first of all, we need to convert the data into money data type then format it into any currency using culture.
Given below is the solution.

SELECT
[Col_ID]
,[Col_varchar]
,FORMAT(TRY_CONVERT(money,[Col_varchar]),'C','en-US')
AS [Varchar_to_currency]
FROM [tbl_sample]
GO