7

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.

7

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.

  • Hi Kenneth, thanks for the answer, but the user input we can standardise on the way through the application, it's the SQL data we want to compare. So we can 'straighten' all user apostrophes before passing them to the SQL command, but we want to match this to any kind of apostrophe in the values of the Person_Name. – JLo Sep 29 '16 at 9:09
  • 1
    I'm not just straightening the user input. I'm setting up a like comparison. If you look at the variable @user_input right before the actual SELECT then you'll see the characters between the []'s. That means that that particular spot will match any one of the characters between them. – Kenneth Fisher Sep 29 '16 at 13:26
  • 1
    @JLo Again, what you are asking for is exactly what Kenneth's solution here does: it converts any of the 3 apostrophes coming in into a multi-character wildcard -- [‘’'] -- for the LIKE operator. Hence, it doesn't matter what the user passes in, even if they use more than one type in a search term, as each one will match to any of the 3 in the data. – Solomon Rutzky Sep 29 '16 at 13:45
  • Thanks! I did miss that completely, this works perfectly – JLo Sep 30 '16 at 8:55
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.

  • 1
    @JLo I don't see why you would have to store a copy of the data. Kenneth's proposed solution handles all 3 in the existing data. Please see my reply comment to you on his answer for more info. – Solomon Rutzky Sep 29 '16 at 13:46
0

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
                   + '%'
  • 1
    Hi David. 1) We can assume the O.P. is using 8-bit data since the O.P. is using CHAR(145), not NCHAR(145), and because the question is framed as being currently, not potentially, slow. 2) Even if the actual data is NVARCHAR, using CHAR(145) works as it will implicitly convert to NCHAR(8216). 3) A persisted computed column is an entirely unnecessary waste of space both on disk and in memory since you can get an index seek with a LIKE using wildcards, just as long as the pattern doesn't start with one. 4) There is no reason to do either hack since Kenneth's answer handles all cases. – Solomon Rutzky Sep 29 '16 at 19:31
  • @srutzky: 1) exactly, so the codepage in use becomes an issue, the curly quotes could end up being somewhere other and 145/146 (though they are most likely there or have been translated to 39 already, but it is worth considering for international apps), 3) the index will not work no matter where the wildcards are(n't) if the functions are applied to the column as they arein the example (WHERE FUNCTION({column}) = {input}) not the other side of the comparison – David Spillett Sep 30 '16 at 11:25
  • More @srutzky: ... But yes: 4) translating the user input in the way Kenith does (his answer wasn't there when I started typing mine) is definitely much cleaner in the single input case, I was over complicating things! The hack might have some relevance if trying to join rather than filter on one value. I'll have a think when I'm not at work and either reword or remove my answer. – David Spillett Sep 30 '16 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.