How to replace embedded commas in a string in a table

Question

I imported 20K lines of data from a CSV file into a table (wline varchar(4000)). For any field that has surrounding double quotes, is there a way to write in T-SQL a routine to find embedded commas between dbl-quotes, and change them to something else (like a caret), then I can remove all the dbl-quotes?

E.g., 123,345,serg,65,rthstrh,"erwfre, sertert", 345q345,ergerg, "123 anwhere, CA", waefwaef, ertwert

                  change to:                 ^                                                              ^

Answer 1

How did you import the file? If you import the file with BULK INSERT, you can specify FORMAT=CSV, and BULK INSERT will nice divide the files into columns.

The operation you suggest is definitely nothing I would like to do in in T-SQL. It is not well-equipped for advanced string manipulation.