well the obvious fix is hard work. you've inherited an app that didn't normalize these fields, and now you need to sanitize and normalize it after the fact...it's not fun.

1. you have to create a master table for the possible values of AppName, with an identity primary key/unique constraint on Appname., making sure to add an acronym for versatility matching.

my wild guess:

CREATE TABLE [dbo].[APPNAMELOOKUP] (

[APPNAMEID] INT IDENTITY(1,1) NOT NULL,

APPACRONYM VARCHAR(3),

[APPNAME] VARCHAR(100) NOT NULL,

CONSTRAINT [PK__AppNameLookup__11C2C212] PRIMARY KEY CLUSTERED (AppNameID),

CONSTRAINT [UQ__AppNameLookup__12B6E64B] UNIQUE NONCLUSTERED (AppName))

2. you have to go through all the descriptions and select only ONE per "real" appname.

3.Whatever application that allows users to freetext an appName must now be modified to select the value from a drop down menu instead.

4. Your existing tables now need new column , which now point to the new table as a foreign key, instead of having descriptions

5. that means a migration by analysis to update the new column to the "correct" value for the Designated Appname. This would probably be a series of scripts, That you custom write yourself, that updates based on comparing charindex like above, then trying for partial matches, and then by 3 letter acronyms, and then finally, for any that did not get caught in the above scripts, a stack of manually assigning records based on eyeball analysis.