Standardise Abbreviations

Posted on 2011-10-18
Last Modified: 2012-06-21
I will be getting a list of company names.
I want to abbreviate certain string in the name to choice 1.
I may find either the full string which I want to shorten to choice1.  Alternatively they may have used choices 2, 3 or 4 which must be changed to choice1.
For example Rentenfonds and Rtfd should be changed to  Rentfds.

Where a choice1 abbreviation has different meanings in the same market it is ambiguous and should be ignored.  For example CP has 2 meanings in Brazil and should be ignored.

Where a choice1 abbreviation has different meanings in one market and a different meaning in "ALL" then expand it according to it's meaning for "ALL" for every market besides the one where it has a different meaing.
For example TR should be expanded to Trust for all marekts besides UK.  In the UK it should be expanded to Total Return.
Full Name           Choice 1   Choice 2      Choice 3 Choice 4 Market

Companies                Cos                                  ALL
Credit Suisse          CP             Curto P                                     Brazil
Curto Prazo                 CP                                  Brazil
Corto Plazo                 CP                                  Spain
Conta de referencia CR                                   Brazil
Credito Privado        CrdPri     C P                                                           Brazil
Credit                  Crdt                                  ALL
Crecimiento                 Crec                                 Spain
Rentenfonds                Rentfds    Rentfds      Rentfd        Rtfd              Germany
Republic                  Rep      Repub                            ALL
Reserve/Reserves    Res      Rsv(s)              Rsrv(s)                     ALL      
TR                              Trust                                                                          ALL
TR                              Total Return                                                              UK
Question by:AlHal2
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    FWIW, ...

    In a true "Normalized" system each "Value" should only have one meaning, else you should be using a different abbreiviation.

    Also you should be referencing a Primary Key Numeric field, (ex.: Autonumber), not the text value.
    This means that you "could" have two two abbreviations that were the same "textually", ...but since you would be referencing the numeric (unique) value, it would not matter.

    To do what you are asking for here would require far to much work (in implementation and maintenance) to allow for each exception contingency.

    In other words, just adding records to a table should not have to involve complex logic.

    How familiar are you with the rules of database design (Normalization, Relationships, key fields, ...etc)?


    Author Comment

    I know about relationships (primary and foreign key constraints).  Not so hot on normalisation.  I think it's to do with each data item appearing once.  We use it, but it often seems to cause as many problems as it solves.

    The problem is the suppliers of the data are using this system of abbreviations.  Perhaps I should just use dynamic SQL or some other method to generate replace statements eg
    @parameter =replace(@parameter,Fullname,choice1)
    and exclude ambiguous choices (not sure exactly how).
    LVL 74

    Accepted Solution

    <Perhaps I should just use dynamic SQL or some other method to generate replace statements eg>
    You can, but just from a table level data entry standpoint, this, to me, seems much too complex.
    The logic might be something like this:
    If [Country]="Spain" and [Abbrev]="CP" Then
        (Ignore the Abbreviation)
    ElseIF [Country]="Brazil" and [Abbrev]="CP" Then
        (Use the Abbreviation)

    Again, just to do data entry, this seems a bit much...

    Perhaps you should investigate using the standard abbreviation, but concatenate the first letter of the Country, then use some combination of the standard abbreviation and the composite value...?
    CPB (For Brazil)
    CPS (For Spain)

    Then you could display the standard value, but reference the composite vale, ...or vice versa.

    Up to you really....



    Author Closing Comment


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now