Matching left and right single-quotes used as apostophes
The best way to handle your issue (and avoid SQL injection) is to pass in your user input as a variable. Since you are using a LIKE
you can do something like this:
CREATE TABLE #person (person_name nvarchar(50))
INSERT INTO #person VALUES (N'Bob'),(N'Bo''b'),(N'Bo‘b'),(N'Bo’b'),(N'Bo#b'),(N'Bo^b')
DECLARE @user_input nvarchar(50) = 'Bo’b'
SET @user_input = REPLACE(
REPLACE(
REPLACE(@user_input, N'‘', N''''),
N'’', N''''),
N'''', N'[‘’'']')
-- @user_input now == Bo[‘’']b
SELECT person_name
FROM #person
WHERE person_name LIKE @user_input
Basically this replaces all of the different 's with a single type (the ') and then puts []'s around all three so that they get used in the LIKE
.
I could have sworn that I saw these characters being equated somewhere but now I can't find it. I checked all collations in both SQL Server 2012 and 2014, and none of them equate CHAR(39)
to either of the other two. So forget that initial idea.
Still, one option, if the exact type of apostrophe is not of specific importance, is to just update the data:
UPDATE person
SET person_name = REPLACE(...)
...to convert CHAR(145)
and CHAR(146)
into CHAR(39)
. Then you don't have to do anything programmatically. You just need to check the data every once in a while, or create a trigger to translate these into CHAR(39)
upon INSERT
or UPDATE
.