How can I replace a `'` in a string in SQL?

Question

I have to replace special characters in SQL.

My problem is with ', because it is used for start and end of string in SQL.

I tried:

ID = REPLACE(ID, ''', '') ID = REPLACE(ID, "'", "")

But both not worked. What should I do?

This is one of those things where you are almost certainly taking the wrong approach: There is basically never be a need to do this. If you're passing values in to SQL Server, you should be using parameterized queries (in which case escaping is unnecessary). If you're passing values around within SQL Server -- which presumably would be the case here, since you're trying to do this with a value already in SQL -- just use the fields or variables. There's (virtually) no use cases where generating dynamic SQL is needed, and doing so is an almost sure-fire way to lead to vulnerabilities. — gregmac
– gregmac, Commented Jun 27, 2019 at 5:55

Arulkumar · Accepted Answer · 2019-06-27 05:28:35Z

ID = REPLACE(ID, '''', '') will work.

Demo with sample data:

DECLARE @ID AS VARCHAR(10) = 'Test''data'; SELECT @ID, REPLACE(@ID, '''', '')

it will remove the single quote from the given string.

Programnik · Accepted Answer · 2019-06-27 05:29:26Z

Either Use the char function and ascii code:

ID = REPLACE(ID, char(39), '')

or double down on the single quotes:

ID = REPLACE(ID, '''', '')

2 Answers 2