CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012
SQL Developer
SQL Developer

Reputation: 69

How to remove double quotes within a column in SQL server 2012

I need to remove double quotes within a column value. For example, I have a column in table which has values as below

  1. "Testing Name ("Inc")"
  2. "Testing, "Trust" ("Inc")"

I need to remove the double quotes surrounding the "Inc" & "Trust" and column values should resemble as below

  1. "Testing Name (Inc)"
  2. "Testing, Trust (Inc)"

I tried with REPLACE() function. But it replaces all the double quotes in a value. But I want to retain the quotes at start and end of the value. Kindly help

Upvotes: 5

Views: 14943

Answers (3)

Drishya1
Drishya1

Reputation: 269

Following code checks if there is a '"' at the start and end of the string and then replaces '"' and concatenates '"' to start and end of the string. If the string doesn't have '"' at the start and end of the string, it just replaces '"' in all the positions of the string and does not concatenate it to start and end.

CREATE TABLE #TAB (COLUMN_VALUE VARCHAR(50))

INSERT INTO #TAB
SELECT '"Testing Name ("Inc")"'
UNION ALL
SELECT '"Testing, "Trust" ("Inc")"'
union all
select 'Testing Name ("Inc")'
union all
SELECT 'Testing, "Trust" ("Inc")'
union all
SELECT 'Testing, "Trust" ("Inc")"'

select * from #TAB

select 
    case when charindex('"',column_value,1)=1 and charindex('"',column_value,len(column_value))=len(column_value) 
         then '"'+REPLACE(COLUMN_VALUE,'"','') +'"' 
         else REPLACE(COLUMN_VALUE,'"','') 
    end as ClenedString
from #TAB;

Upvotes: 1

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

IF start and end of the value will always contains double quotes, then try the below script.

SELECT  '"'+REPLACE( [column],'"','')+'"'
FROM    [table]

Upvotes: 1

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Use Replace to remove double quotes from string. And then Prefix, Suffix with double quotes.

 DECLARE @VAR VARCHAR(50)='"Testing Name ("Inc")"'

 CREATE TABLE #TAB (COLUMN_VALUE VARCHAR(50))

 INSERT INTO #TAB
 SELECT '"Testing Name ("Inc")"'
 UNION ALL
 SELECT '"Testing, "Trust" ("Inc")"'

Now hit below SELECT statement

 SELECT COLUMN_VALUE, '"'+REPLACE(COLUMN_VALUE,'"','') +'"' as NEW_COLUMN_VALUE FROM #TAB

And the result will be

+----------------------------+------------------------+
|        COLUMN_VALUE        |    NEW_COLUMN_VALUE    |
+----------------------------+------------------------+
| "Testing Name ("Inc")"     | "Testing Name (Inc)"   |
| "Testing, "Trust" ("Inc")" | "Testing, Trust (Inc)" |
+----------------------------+------------------------+

Upvotes: 6

Related Questions