December 6, 2016 at 8:48 am
How to create a loop to go through all fields, all cells to replace "," with " " (space)?
For example, there are 70 fields in a table ORDER.
For some reasons, there are one or more "," in any cells like below:
aaa,bbbccc,ddd
qqq,www
After running script, cell should be
aaa bbbccc ddd
qqq www
December 6, 2016 at 8:57 am
adonetok (12/6/2016)
How to create a loop to go through all fields, all cells to replace "," with " " (space)?For example, there are 70 fields in a table ORDER.
For some reasons, there are one or more "," in any cells like below:
aaa,bbbccc,ddd
qqq,www
After running script, cell should be
aaa bbbccc ddd
qqq www
Start by taking a look at the UPDATE statement and the REPLACE function.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 6, 2016 at 9:02 am
Why do you want to Loop? That would be incredibly inefficient.
Are you sure that you want to do this to all fields, regardless? Do I assume that you don't want to remove fields that aren't of type varchar/nvarchar/char/nchar?
I would definitely do some testing, but this should work:
USE [DBName];
GO
SELECT *
FROM orders o;
DECLARE @SQL VARCHAR(MAX) = '';
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 = 'UPDATE orders
SET ' + (LEFT(@SQL, LEN(@SQL)-1)) + ';';
EXEC(@SQL);
SELECT *
FROM orders o;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 6, 2016 at 9:14 am
declare @schema sysname='person',@objectName sysname ='person'
Declare @string nvarchar(4000) ='update '+quotename(@schema)+'.'+quotename(@objectName)+' set '
select @string=@string + quotename(c.name) +'= replace('+quotename(c.name)+','','','' ''),' from sys.columns C
inner join sys.objects o on o.object_id = c.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
where c.system_Type_id in (231,167,175,239)
and o.name = @objectNAme and s.name = @schema
select left(@string,len(@string)-1)
December 6, 2016 at 11:55 am
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)
December 6, 2016 at 12:21 pm
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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 7, 2016 at 1:55 am
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
December 7, 2016 at 6:16 am
Thank you for hold hand help, Thom A.
It really works now.
This script is so great and help. I will study and save it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply