drew.allen (12/6/2016)
adonetok (12/6/2016)
I tried the way from SSChasing Mays but got an error once I changed table name from "orders" to my table's name.Where is "near '-'" from script?
(352866 row(s) affected)
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
(352866 row(s) affected)
I think you mean Thom A. SSChasing Mays is a title. The name is above the avatar.
It's saying that there is an error near the minus sign. Since there is only one minus sign in the entire script, it's somewhere near here:
SET @SQL = 'UPDATE orders
SET ' + (LEFT(@SQL, LEN(@SQL)-1)) + ';';
Of course, Thom A is using dynamic SQL, so it's also possible that the error is in the generated code, which we can't see. For example, if your column names contain '-' then this will also generate an error, because Thom A's code hasn't properly accounted for column names that include anything other than A-Z, 0-9, and _. When creating your dynamic SQL, you should use the Quotename function to properly quote your column names.
Drew
I didn't want to hold the OP's hand all the way to the finish line, however... 🙂
USE DevTestDB;
GO
SELECT *
FROM orders o;
DECLARE @SQL VARCHAR(MAX) = '';
SET @SQL = 'UPDATE orders
SET ';
SELECT @SQL = @SQL + '
[' + c.name + '] = REPLACE([' + c.name + '], '','','' ''), '
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ct on c.system_type_id = ct.system_type_id
WHERE t.name = 'orders'
AND ct.name IN ('varchar','nvarchar','char','nchar');
SET @SQL = (LEFT(@SQL, LEN(@SQL)-1)) + ';';
--EXEC(@SQL);
SELECT @SQL;
SELECT *
FROM orders o;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk