CODEWITHSUNDEEP
sqlsql-server
Chipmunk_da
Chipmunk_da

Reputation: 507

SQL Server remove double quote text qualifiers from column data

I have loaded a table where the column names and the data in every column is qualified with " (double quotes) e.g. "abcd". I need to now produce a 'clean' table with these double quotes wrappers removed from the column headers and the data. Note that the underlying data itself can have double quotes in it e.g. "abc " d". I've included sample data below which can be copy-pasted in a Notepad file (ANSI encoded). Note that the column delimiter is ¬.

Removing the quotes from the column headers should be fine as I can just do a find and replace in the script when creating the new table. But, for the data, the only way I can think of is to use something like select replace('"abc " d"', '"', ''). However, this would also remove the 'actual' quotes in the data and moreover is pretty crude.

Can anyone provide a better solution please?

Sample Data

"ID"¬"X_REF"¬"STRAW_ID_LOCAL"¬"COMMENT"
"B9890"¬"99999      "¬"ALTER   "¬"Dullovi Center FARG cascade                                                                                                          "
"J555"¬"2134 "¬"ERHARAM  "¬""PREM Center                                                                                                              "

Upvotes: 0

Views: 661

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use a case expression:

select (case when col like '"%"'
             then substring(col, 2, len(col) - 1)
             else col
        end)

Upvotes: 1

Related Questions