Q:
MSSQL Server 2008 Management Studio - Replace Quotes with COMMA
I'm using the management studio to view a table with a column that contains quotes. I need to see them as commas. Is there a way to replace them? I can edit the column in the properties, but it has to be saved with the original quotes in order to preserve the data.
A:
I had a similar issue with a string containing commas within a string. The string I needed to convert was not simple to convert, however the solution was easier than some of the other answers posted.
In the screenshot below you can see the result of what I did, which I believe is pretty close to what you want.
What I did was copy the string into notepad++ and then replace all single quotes with double quotes and all double quotes with single quotes.
Once the quotes were changed then open up the text file using notepad++ and save it as a CSV file. This will allow you to open the file up in your SQL Server Management Studio and the string will be there as commas.
Once you do this, you can then just change the text you need to and save.
Use the Replace function in the database, give it a search string of something like "'" and a replacement of "," (or whatever other delimiter you wish to use)
Example:
select replace('1234','12',',');
Result: 1,234
This will work in all versions of SQL Server that I'm aware of.
You should be able to do this through the client (SSMS or TSQL) by escaping the quote characters in the text before inserting them into your column. For example, if you wanted to convert your quotes into commas, you could do it like this:
UPDATE MyTable
SET MyColumn = replace(MyColumn, '''', ',');
This would replace all single quotes with commas in the column. Note that the double-quotes around the " MyColumn =" are required to make this work, otherwise the result would be: "MyColumn" =, which would be obviously meaningless.
A range of current and former CIA employees have become whistleblowers and sent their info to members of the House Intelligence Committee, all of whom want the opportunity to question John Brennan about his role in concocting the dossier of dirt on President
Related links:
Comments