Matching left and right single-quotes used as apostophes

Question

I have four columns containing names and want to search these using a LIKE in a Microsoft SQL Server environment.

The complication comes that names may include left and right single-quotes / angled apostrophes (i.e. ‘ and ’, char(145) and char(146) respectively), which should match a straight apostrophe (i.e. ', char(39))

Doing the following is very slow:

SELECT person_id
FROM person
WHERE REPLACE(
          REPLACE(
              person_name, 
              CHAR(145), 
              CHAR(39)
          ), 
          CHAR(146), 
          CHAR(39)
      ) LIKE '{USER_INPUT}'

As explained in SQL replace statement too slow on Stack Overflow, this is because the use of REPLACE makes the statement unsargable.

Is there a way that SQL Server can handle situations like this in a better way?

One solution which has been proposed is to have the application generate a 'searchable' value which concatenates all of the fields (person_name, person_surname, person_nickname, etc.) and converts the problematic characters at the point of editing. This could be effectively indexed and searched. Storing this data in a separate SQL table/column would require less application rewrite than implementing a full NoSQL solution like Lucene.

The example above is a simplification: the query doesn't literally get built as I explained above and we do implement SQL injection (and other) protections.

The question is how to replace the angled-apostrophes with straight ones in the table data. To clarify:

• User supplies O‘Malley - this should match both O‘Malley or O'Malley
• User supplies O'Malley - this should match both O‘Malley or O'Malley

We need to replace the SQL data, not the user's input. We can convert the user input on the way through the application so that if they input angled apostrophes we change them to simple apostrophes before passing in to SQL. It's the data in SQL we need to standardise.

Unfortunately the data must stay in the database as the correct angled bracket, but when we do the search we need to match them all against straight apostrophes.

Answer 1

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.

Answer 2

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.

Answer 3

Be careful: are you sure you really have Unicode there and not some 8-bit codepage? While 145 is displayed as a quite in many fonts it isn't officially that in the unicode standard (145 & 146 are listed as PRIVATE USE ONE and PRIVATE USE TWO respectively: http://www.fileformat.info/info/unicode/char/0091/index.htm). If you have 8-bit text then that character could become anything else in another codepage (145 is æ in some) so you may have character translation issues in your application. Curly single quotes are 2018 and 2019 respectively in unicode.

Such possible encoding issues aside: wrapping a function around the column you are searching will make the filter unsargable as you have already deduced. In this instance three is very little you can do about it without structural changes.

As a minimum, if you have an index on person_name you can hopefully turn a table scan into an index scan (though depending on other filters, including those implied by joins and so forth, in the query it might not be quite as easy as that).

To try speed things up properly and turn those potential scans into index seeks, if you have the power to control the table layout as well as the querying code you could create a persisted computed column that contains an "canonical" string (in this case the canonical representation would be produced by something like person_name_canon = REPLACE(REPLACE(person_name, CHAR(145), CHAR(39)), CHAR(146), CHAR(39)))), ensure that column is involved in relevant indexes, and when searching by that column you can do WHERE person_name_canon LIKE REPLACE(REPLACE(@user_input, CHAR(145), CHAR(39)), CHAR(146), CHAR(39))).

If you can't add the computed column but there is a useful index on person_name a slightly hacky approach might be to do something like:

WHERE REPLACE(REPLACE(person_name, CHAR(145), CHAR(39)), CHAR(146), CHAR(39))) LIKE @user_input
AND   person_name LIKE CASE
                       WHEN @user_input LIKE '%''%'
                       THEN SUBSTIRNG(@user_input,1,CHARINDEX('''',@user_input)-1)
                       ELSE @user_input
                       END
                   + '%'

Please note that I have not tested this at all so it may not have the desired effect but in theory that second clause should result in a sargable partial filter, so if @user_input is Michael O'Brien it can use the index to find text starting with Michael O then the other filter will throw out those not fully matching after all the replacements on the stored data are done.

You may also need to account for the different quote characters in the input if that isn't being normalised in your business logic before the search, like so:

WHERE REPLACE(REPLACE(person_name, CHAR(145), CHAR(39)), CHAR(146), CHAR(39))) LIKE @user_input
AND   person_name LIKE CASE
                       WHEN @user_input LIKE '%''%'            THEN SUBSTIRNG(@user_input,1,CHARINDEX('''',@user_input)-1)
                       WHEN @user_input LIKE '%'+CHAR(145)+'%' THEN SUBSTIRNG(@user_input,1,CHARINDEX(CHAR(145),@user_input)-1)
                       WHEN @user_input LIKE '%'+CHAR(146)+'%' THEN SUBSTIRNG(@user_input,1,CHARINDEX(CHAR(145),@user_input)-1)
                       ELSE @user_input
                       END
                   + '%'

Answer 4

Thanks to @Kenneths' answer, I wrote this version, which should deal with all kinds of apostrophe-like characters:

--replace all strange apostrophes with the normal one (Unicode U+0027, ASCII 39)
--and then replace it with the whole collection of possible apostrophes, used in the search
set @user_input =
    replace(
        replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
            ltrim(rtrim(@user_input)),
        '`', ''''),
        '´', ''''),
        'ʹ', ''''),
        'ʻ', ''''),
        'ʼ', ''''),
        'ʽ', ''''),
        'ʾ', ''''),
        'ʿ', ''''),
        'ˈ', ''''),
        'ˊ', ''''),
        'ˮ', ''''),
        'ʹ', ''''),
        '΄', ''''),
        '՚', ''''),
        '᾽', ''''),
        '᾿', ''''),
        '‘', ''''),
        '’', ''''),
        '‛', ''''),
        '′', ''''),
        'Ꞌ', ''''),
        'ꞌ', ''''),
        '＇', ''''),
    '''', '[''`´ʹʻʼʽʾʿˈˊˮʹ΄՚᾽᾿‘’‛′Ꞌꞌ＇]')

Anyway it should be better to clean your data in the table before, instead of dealing with these oddities.