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%'
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.
Abbrevs with columns
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
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
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
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+'%'