J Alc J Alc - 7 months ago 29
SQL Question

Abbreviation in where clause

Treat abbreviation like Sta. equals to Santa, or Ma. equals to Maria or Ma.

SELECT * FROM Table1 A WITH(NOLOCK) WHERE A.Name LIKE '%ma%'


This query is for Maria, but the problem is it also included a name contains
marie


Case 1: User input
ma
the result would be
maria
,
ma
, and
ma.
and vice versa.

Case 2: User Inputs
Maria
the outputs would be
Ma. Teresa
or
Maria Teresa
or
Ma Teresa
and same goes on
sta
scenario.

Case 3: User input
sta
the result would be
santa
,
sta.
and
sta
and vice versa.

Case 4: User input normal name (Jose) without abbreviation the result would be like
Jose


Any idea on this problem? or what is the best approach for this problem?

Thank you.

Answer Source

One approach is to have a list of all abbreviations and what they correspond to.

Essentially, build a separate table that would expand abbreviation into full name.

Table Abbrevs with columns Abbrev, Name.

For your example it will contain these rows:

Abbrev Name
ma     Maria
ma     Ma.
ma.    Maria
ma.    Ma
sta    Santa
sta    Sta.
sta.   Santa
sta.   sta

Then look for the string that user provided plus all possible synonyms of this name. Something like this:

DECLARE @ParamInput nvarchar(255);
SET @ParamInput = 'ma';

SELECT *
FROM YourTable
WHERE
    YourTable.Name = @ParamInput
    OR YourTable.Name IN
    (
        SELECT Abbrevs.Name
        FROM Abbrevs
        WHERE Abbrevs.Abbrev = @ParamInput
    )
;

You may let user define and edit the Abbrevs table.


If both user input and the table may have abbreviations, and you need to use LIKE, not a simple = for searching, then I'd do something like the following.

There will still be a separate table that expands abbreviations (or, rather a list of possible abbreviations of a specific name):

Abbrev Name
ma     Maria
ma.    Maria
sta    Santa
sta.   Santa

In addition to that I would have a function that normalizes the given name. The function would search for possible abbreviations in the given name and expand them.

For example, given Ma. Teresa, the function will return Maria Teresa using the Abbrevs table.

Your main table has a field Name with original name that can have abbreviations. I'll add another (computed or persisted) column NormalizedName, which would be populated by this function. In practice I made such column as a normal persisted column and wrote a trigger that updated its value when the main Name column changes. One step of normalization was to bring all strings to upper case. You may build some other rules into it, like discarding all dots, commas, dashes and other such symbols, replacing double spaces with single space, etc.

You show to the user only original Name values.

When a user provides a name to search for, I normalize the given name using the same function and search for normalized value in the NormalizedName column with a simple

WHERE NormalizedName LIKE '%'+@ParamNormalizedName+'%'