Script to replace ","

  • 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

  • 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.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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]

  • 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

  • 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)

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • 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)

  • 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

  • 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

  • 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