SQL Select Query - Removing Duplicates/Misspelled Data

Question

Pulling data from a cmdb into another repository. Problem is the cmdb data has misspelled/duplicate records (e.g., some assets have a Department Name as Marketing, or Markting, or Marketing&amp -- when they are all just in Marketing). Want to run a select query that displays all incorrectly named department records as the single, correct name. Any help on how to approach this?

Answer 1

You can use CASE in to display "marketing" for its wrong entries. But query can be complicated depending on variations.

Better + easier way is a global search and replace in column. Following article describes it:

http://www.codecandle.com/articles/sql/update/483-sql-update-with-global-search-and-replace.html

Cleaning duplicate rows, following article may help:

http://www.codecandle.com/articles/sql/windowing/503-deleting-duplicate-rows-using-windowing.html

Answer 2

I'm sure this is passed but http://openrefine.org/ would probably help you clean the messy data.

Answer 3

you can use the SELECT DISTINCT statement is used to return only distinct (different) values.

you should use distinct keyword before coloumn names in select statement.

e.g: select distinct name (Coloumn name) from table name;