SQL Server : create regex search

Question

I am trying to use a regular expression to do an accent insensitive search on an accent sensitive column.

I cannot change the column to an accent insensitive collation because the column is unique and we need to allow both 'Jose' and 'José' to be entered.
I cannot coerce the collation within my query because the table holds a ton of data and coercing it will cause it to do an index scan instead of a seek and the query times out
I cannot add a new non-unique column that uses an accent insensitive collation. This would avoid the 2 issues above because the original column could be accent sensitive and unique and I would be able to do the search on the new column and use the index on it, however I'm being told I can't do this because it would cause data duplication.

So, I'm trying to do this using a regular expression search on the accent sensitive column. In my application I take in whatever string the user entered and I modify the string in such a way that for this input

"Jose"

I get the output

"J[òóôõöø][sš][eèéêë]%"

Using this string to search works. It finds "Jose" and "José" and it searches with the correct index and doesn't time out.

The only issue I have now is with the darn æ character.

If the database contains the values "aéro" & "æro" and the user enters "aero", currently my application will generate the search strings "[aàáâãäå][eèéêë]r[oòóôõöø]%" But this will only be a match for "aéro". It will not match "æro". By default in sql server it already treats "ae" the same as "æ" whether the collation is accent sensitive or insensitive, so not modifying the user's input would return "æro" as a match, but would not return "aéro" as a match.

Does anyone have any ideas how I can do a regular expression search in SQL that will match both "aé" and "æ"?

SELECT *
FROM mytable
WHERE name LIKE '[aàáâãäå][eèéêë]%'

Gosh without a query or something to work with here we can't offer much. And of course this is going to force you to add a collation in the query and make it nonSARGable. There really isn't going to be much of a way around that. Here is a great place to start. spaghettidba.com/2015/04/24/… — Sean Lange
– Sean Lange, Commented Sep 26, 2016 at 16:55
You might be able to use full-text search to good effect; here's somewhere to start investigating: Sql-server Full Text CONTAINS + COLLATE to ignore accents issues. Also, this question might get more attention from the people who know about these things at Database Administrators Stack Exchange. — Andrew Morton
– Andrew Morton, Commented Sep 26, 2016 at 18:20

Edmond Quinton · Accepted Answer · 2016-09-27 18:20:11Z

2

Unfortunately, SQL Server LIKE statement does not support the full regex functionality. You can however achieve the result you are looking for by modifying your current query as follow:

DECLARE @primarySearch VARCHAR(100) = '[aàáâãäå][eèéêë]r[oòóôõöø]%';
DECLARE @secondarySearchKey VARCHAR(100) = REPLACE(@primarySearch, '[aàáâãäå][eèéêë]', 'æ')

SELECT *
FROM    mytable
WHERE   Name LIKE @primarySearch OR Name LIKE @secondarySearchKey

In the modified version we add a second OR comparison check that simply replaced [aàáâãäå][eèéêë] combination with the æ character. This will add some additional overhead to your query but I doubt it will be significant.

UPDATE:

The above solution will not work for multiple occurrence [aàáâãäå][eèéêë] combination as per Nick’s comments. You could however perform an initial wider search that only matches the pattern up to the first occurrence of the [aàáâãäå][eèéêë] character combination. Once the initial match is perform you can normalize the text by replacing the occurrence of the ‘æ’ character with ‘ae’ string. Once this is done you can perform the refined search as per following example:

DECLARE @primarySearch VARCHAR(100) = '[aàáâãäå][eèéêë]_[aàáâãäå][eèéêë]_[aàáâãäå][eèéêë]%';

IF (@primarySearch LIKE '%\[aàáâãäå\]\[eèéêë\]%' ESCAPE '\') OR  @primarySearch LIKE '%æ%' 
BEGIN

    DECLARE @firstOccurrence INT = CHARINDEX('[aàáâãäå][eèéêë]', @primarySearch);
    DECLARE @initialSearchKey VARCHAR(100) = LEFT(@primarySearch, @firstOccurrence + LEN('[aàáâãäå][eèéêë]') - 1) + '%'
    DECLARE @initialSearchSpecialCaseKey VARCHAR(100) = REPLACE(@initialSearchKey, '[aàáâãäå][eèéêë]', 'æ');

    SELECT @initialSearchKey AS IntialKey;
    SELECT @initialSearchSpecialCaseKey AS SpecialCaseKey ;

    WITH InitialSearch AS  
    (
        SELECT  Name
                ,REPLACE(Name, 'æ', 'ae') AS NormalizedName
        FROM    mytable
        WHERE   Name LIKE @initialSearchKey OR Name LIKE @initialSearchSpecialCaseKey
    )
    SELECT  Name
    FROM    InitialSearch
    WHERE   NormalizedName LIKE @primarySearch

END
ELSE
BEGIN

    SELECT  Name
    FROM    mytable
    WHERE   Name LIKE @primarySearch
END

edited Sep 27, 2016 at 18:20

answered Sep 26, 2016 at 18:22

Edmond Quinton

1,74910 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nick Over a year ago

This doesn't really work when the characters "ae" appear multiple times. When n = the number of times that "ae" appears in a string, you'd have to do 2^n searches. [aàáâãäå][eèéêë]_[aàáâãäå][eèéêë]_[aàáâãäå][eèéêë], [aàáâãäå][eèéêë]_[aàáâãäå][eèéêë]_æ, [aàáâãäå][eèéêë]_æ_[aàáâãäå][eèéêë], [aàáâãäå][eèéêë]_æ_æ, æ_[aàáâãäå][eèéêë]_[aàáâãäå][eèéêë], æ_[aàáâãäå][eèéêë]_æ, æ_æ_[aàáâãäå][eèéêë], & æ_æ_æ

Edmond Quinton Over a year ago

@Nick I see what you mean, unfortunately I don’t know of an elegant solution that can deal with multiple occurrences of the [aàáâãäå][eèéêë] combination. I will updated my answer with a possible solution for you.

Collectives™ on Stack Overflow

SQL Server : create regex search

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related