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 is function to check if the search contains ma, ma. You show to the user only original Name values. 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. One step of normalization was to bring all strings to upper case. 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. I'll add another (computed or persisted) column NormalizedName, which would be populated by this function. Your main table has a field Name with original name that can have abbreviations. Teresa, the function will return Maria Teresa using the Abbrevs table. The function would search for possible abbreviations in the given name and expand them.įor example, given Ma. Would creating a dictionary for converting the other direction be the best. I do not see a way to go the other direction though. I see that to convert from month number to abbreviated month name you can just do calendar.monthabbrnum. In addition to that I would have a function that normalizes the given name. month name to month number and vice versa in python.
![vice versa abbreviation vice versa abbreviation](https://s1.manualzz.com/store/data/004321880_1-8da0abbc02b3bd6ce1ab87c522d181f6-360x466.png)
There will still be a separate table that expands abbreviations (or, rather a list of possible abbreviations of a specific name): Abbrev Name 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. WHERE Abbrevs.Abbrev = may let user define and edit the Abbrevs table. Something like this: DECLARE nvarchar(255) Then look for the string that user provided plus all possible synonyms of this name.
#VICE VERSA ABBREVIATION FULL#
One approach is to have a list of all abbreviations and what they correspond to.Įssentially, build a separate table that would expand abbreviation into full name.įor your example it will contain these rows: Abbrev Name