SQL Snippets: Manage Data Containing an Apostrophe

This post is part of the series on SQL Snippets.

I was doing some work with a client recently and they had an issue with some data in SQL Server which they needed change, but weren’t able to do it through the application.

The issue was that they had some names in a column marked as a key field which contained an apostrophe and it was causing them problems; the data should not have had an apostrophe, but they a user had managed to do it and they wanted to do an update to remove it.

There SQL was a little more limited than mine so they asked for assistance. I created the following SQL as an example for them on how data with apostrophe in can be managed.

The example shows how to insert data containing an apostrophe as well as two ways of changing data; one was doing it globally an the other just changing a specific record.

The key to this is that in Microsoft SQL Server you can use one apostrophe to escape another.

-- CREATE TEMP TABLE
CREATE TABLE #Temp(
	USERNAME VARCHAR(15)
)
GO

-- INSERT DATA INTO TEMP TABLE
INSERT INTO #Temp (USERNAME) VALUES ('AJ''ones')
GO

-- SELECT DATA TO CHECK
SELECT * FROM #Temp
GO

-- RUN ONE OF THE TWO UPDATE STATEMENTS
-- remove single quote FROM one record
UPDATE #Temp SET USERNAME = REPLACE(USERNAME, '''', '') WHERE USERNAME = 'AJ''ones'

-- remove single quote FROM all records
UPDATE #Temp SET USERNAME = REPLACE(USERNAME, '''', '') WHERE USERNAME LIKE '%''%'
GO

-- SELECT DATA TO CHECK
SELECT * FROM #Temp
GO

-- DROP TEMP TABLE
DROP TABLE #Temp
GO