SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF

-- Declare varibles 
DECLARE
@SQLStmt NVARCHAR(MAX),
@FieldName NVARCHAR(50),
@StringReplacement NVARCHAR(200),
@StringToFind NVARCHAR(200),
@maxPK INT,
@pk INT,
@Quote NVARCHAR(1),
@WorkingNamingTable NVARCHAR(256),
@WorkingDataloadFile3 NVARCHAR(256)

SELECT @WorkingNamingTable = ?
SELECT @WorkingDataloadFile3 = ?

--Make sure the temp table has been dropped
--Only for use when testing in SQL mgmt studio
--BEGIN
--    DROP TABLE #StringReplace
--END
SET @Quote = '"'
-- Create temp table 
SELECT @SQLStmt = '
Select PK_ID
INTO   #StringReplace
FROM   @WorkingNamingTable 

-- Get the max ID in the table 
-- table is new each time so do not need to worry about 
-- ID not starting at 1
Select @maxPK = MAX(PK_ID) FROM #StringReplace
SET @pk = 1



-- Loop thru the table 
WHILE @pk <= @maxPK
BEGIN

	-- Preform replacement (note we are replacing in the whole field not just the end)
	SELECT @FieldName = Field from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
	SELECT @StringReplacement = StringReplacement from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)
	SELECT @StringToFind = StringToFind from @WorkingNamingTable WHERE PK_ID = (Select PK_ID From #StringReplace Where PK_ID = @pk)

	
	UPDATE ' + @WorkingDataloadFile3 + '
	SET '  + @FieldName + ' = ' + ' " " + ' + @FieldName +  ' + " " FROM '+ @WorkingDataloadFile3 +'
	WHERE ' + @FieldName + ' IS NOT NULL

	UPDATE ' + @WorkingDataloadFile3 + '
	SET '  + @FieldName + ' = REPLACE(' + @FieldName + ',' + @Quote +  @StringToFind  +  @Quote + ',' + @Quote + @StringReplacement + @Quote + ')
	FROM ' + @WorkingDataloadFile3 + '
	WHERE ' + @FieldName + ' IS NOT NULL
	
	
	Select @pk = @pk + 1
	 
END

SET @pk = 1

-- Loop thru the table 
WHILE @pk <= @maxPK
BEGIN

	UPDATE '+ @WorkingDataloadFile3 +'
	SET '  + @FieldName + ' = LTRIM(RTRIM(' + @FieldName + ')) FROM @WorkingDataloadFile3 
	WHERE ' + @FieldName + ' IS NOT NULL
	 
	 Select @pk = @pk + 1
END'

EXECUTE(@SQLStmt)

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO