DISCLAIMER: The product links on this page are affiliate links which means I get a commission if you purchase anything through them, but it will not cost you anything extra. For more information see my Affiliate Disclaimer page.

Convert Text Data to DECIMAL using REPLACE method

Often we want to take data from a legacy system and get it into a SQL Server database. Here is an example of a conversion process to change a textual dollar amount into a database DECIMAL field. We do this using the REPLACE() method.
--create a table emulating a fixed-width text import
DECLARE @Imports TABLE (
	DATEFIELD char(19) NOT NULL,
	DATAFIELD char(10) NOT NULL )
INSERT INTO @Imports VALUES
('2010:09:09 16:00:46','$    10.88'),
('2010:09:16 13:56:41','$    21.68'),
('2010:09:16 14:05:50','$     5.57'),
('2010:09:19 16:43:31','$    15.33'),
('2010:09:19 18:42:21','$   259.23'),
('2010:09:25 13:50:29','$    73.79'),
('2010:09:25 13:50:37','$    46.92')
 
SELECT	DATAFIELD,
	CONVERT(DECIMAL(10,2),REPLACE(REPLACE(DATAFIELD,'$',''),' ','')) AS [DATAFIELD Converted]
FROM @Imports
Note if the data were already in DECIMAL friendly format you could directly CONVERT it as follows. In the case above you will get the following error due to invalid characters in the date field. CONVERT(DECIMAL(10,2),DATAFIELD)
Error converting data type varchar to numeric.
The REPLACE() method is a string manipulation function which can replace a specific string occurrence with the text you specify every time it appears in your string. You can read more information about REPLACE() on msdn.
Media Format: 
Topics: