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)

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.