There are ~10 different subquestions that could be answered here, but the main question is in the title. TLDR version: I have a table like the example below and I want to replace all double quote marks across the whole table. Is there a simple way to do this?

My solution using cursor seems fairly straightforward. I know there's some CURSOR hatred in the SQL Server community (bad runtime?). At what point (num rows and/or num columns) would CURSOR stink at this?

Create Reproducible Example Table

    DROP TABLE IF EXISTS #example;
    CREATE TABLE #example (
       NumCol  INT
      ,CharCol  NVARCHAR(20)
      ,DateCol  NVARCHAR(100)
      );
      
    INSERT INTO #example VALUES
    (1, '"commas, terrible"',  '"2021-01-01 20:15:57,2021:04-08 19:40:50"'),
    (2, '"loadsrc,.txt"', '2020-01-01 00:00:05'),
    (3, '".txt,from.csv"','1/8/2021 10:14')

Right now, my identified solutions are:

1. Manually update for each column UPDATE X SET CharCol = REPLACE(CharCol, '"',''). Horribly annoying to do at any more than 2 columns IMO.
2. Use a CURSOR to update (similar to annoyingly complicated looking solution at SQL Server- SQL Replace on all columns in all tables across an entire DB

REPLACE character using CURSOR

This gets a little convoluted with all the cursor-related script, but seems to work well otherwise.

-- declare variable to store colnames, cursor to filter through list, string for dynamic sql code
DECLARE  @colname   VARCHAR(10)
        ,@sql       VARCHAR(MAX)
        ,@namecursor CURSOR;

-- run cursor and set colnames and update table
SET @namecursor = CURSOR FOR SELECT ColName FROM #colnames
OPEN @namecursor;  
FETCH NEXT FROM @namecursor INTO @colname;  

WHILE (@@FETCH_STATUS <> -1)  -- alt: WHILE @@FETCH_STATUS = 0
    BEGIN;  
        SET @sql = 'UPDATE #example SET ' @colname ' = REPLACE(' @colname ', ''"'','''')'
        EXEC(@sql); -- parentheses VERY important: EXEC(sql-as-string) NOT EXEC storedprocedure
        FETCH NEXT FROM @namecursor INTO @colname;  
    END;  
 
    CLOSE @namecursor;  
    DEALLOCATE @namecursor;  
    GO  

-- see results
SELECT * FROM #example

Subquestion: While I've seen it in our database elsewhere, for this particular example I'm opening a .csv file in Excel and exporting it as tab delimited. Is there a way to change the settings to export without the double quotes? If I remember correctly, BULK INSERT doesn't have a way to handle that or a way to handle importing a csv file with extra commas.

And yes, I'm going to pretend that I'm fine that there's a list of datetimes in the date column (necessitating varchar data type).

CodePudding user response：

Why not just dynamically build the SQL?

Presumably it's a one-time task you'd be doing so just run the below for your table, paste into SSMS and run. But if not you could build an automated process to execute it - better of course to properly sanitize when inserting the data though!

select 
'update <table> set '  
String_Agg(QuoteName(COLUMN_NAME)   '=Replace('   QuoteName(column_name)   ',''"'','''')',',')
from INFORMATION_SCHEMA.COLUMNS
where table_name='<table>' and TABLE_SCHEMA='<schema>' and data_type in ('varchar','nvarchar')

example DB<>Fiddle

CodePudding user response：

You might try this approach, not fast, but easy to type (or generate).

SELECT NumCol = y.value('(NumCol/text())[1]','int')
      ,CharCol = y.value('(CharCol/text())[1]','nvarchar(100)')
      ,DateCol = y.value('(DateCol/text())[1]','nvarchar(100)')
FROM #example e
CROSS APPLY(SELECT e.* FOR XML PATH('')) A(x)
CROSS APPLY(SELECT CAST(REPLACE(A.x,'"','') AS XML)) B(y);

The idea in short:

• The first APPLY will transform all columns to a root-less XML.
• Without using ,TYPE this will be of type nvarchar(max) implicitly
• The second APPLY will first replace any " in the whole text (which is one row actually) and cast this to XML.
• The SELECT uses .value to fetch the values type-safe from the XML.

Good luck, messy data is always a pain in the neck :-)