Standardise Abbreviations

Posted on 2011-10-18
Medium Priority
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
  • 2
  • 2
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36990627
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

ID: 36991396
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

Jeffrey Coachman earned 2000 total points
ID: 36993116
<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

ID: 37012051

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month3 days, 8 hours left to enroll

599 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