Correct Invalid or Misspelled Values using SQL Server Trigger
On SQL Server using a database table for invalid forms and typos and correct values of string entries, I managed to update table entries dynamically with correct values using a database table trigger as I share SQL codes in this tutorial.
Let's assume that users frequenty provide false entries for some specific values on freetext form elements.
For example, in Address table users enter wrong values like Istanbul, Istanbol, etc. for its correct form İstanbul.
Also assume Philladelpia where the valid entry for mentioned city is Philadelphia.
Database professionals can also name this task as data cleansing as well. Actually what we do in this SQL tutorial, is a simple form of data cleansing using SQL triggers defined on target entry database tables.
Let's create our data model in a development SQL Server database by using an address table and correction table using following SQL script.
create table Address (
AddressId int identity(1,1) not null,
Address nvarchar(255),
City nvarchar(100)
)
go
create table Correction (
CorrectionId int identity(1,1) not null,
falseForm nvarchar(100),
correctForm nvarchar(100),
)
go
insert into Correction select N'Istanbul', N'İstanbul'
insert into Correction select N'Istanbol', N'İstanbul'
insert into Correction select N'Philladelpia', N'Philadelphia'
I'll now create SQL trigger on table Address so that when a new Insert or Update statement is executed and new City column value is one of the wrong forms stored in Correction table, I will be able to Update invalied entries with its valid forms automatically.
Here is the SQL Server trigger for Address table for providing auto-updates of wrong city name entries
CREATE TRIGGER AddressCorrection ON Address AFTER INSERT, UPDATE
AS
BEGIN
update Address
set
City = c.correctForm
from Inserted as i
inner join Correction c
on i.City = c.falseForm
where
Address.City = i.City
END
Let's now test our SQL trigger by entering new rows into Address table using correct forms and typos of City names we have mentioned before and update existing address data using SQL DML language commands.
insert into Address select N'', N'Istanbul'
select * from Address
You see, false entry of the City column is corrected with the city's real spelling o the Address database table
Let's now make an other test by inserting multiple rows in a single INSERT statement as follows.
insert into Address values (NULL, N'Istanbol'),(NULL,N'Philladelpia')
select * from Address
Another test will be updating all İstanbul entries with one of its false forms and updating Philadelphia with a misspelling of it which is not in the correction table.
update Address set City = N'Istanbol' where AddressId in (1,2)
update Address set City = N'Phyladelphia' where AddressId = 3
select * from Address
As seen in the following resultset on SQL Server Management Studio, the misspelled words that already exist on Correction table could be updated with valid representations on Address table City column.
On the other hand, if the misspelled form or the invalid typo is not in the Correction table, the SQL trigger cannot recognize false entry to take action and correct it.
Now let's assume that as the SQL database developer, you have realized that this wrong "Phyladelphia" form is entered frequently into the Address table and you want to correct these entries by inserting a new line to Correction table.
What I could suggest here is to create a new SQL trigger on Correction table this time, so that when a new entry or update is executed on the Correction table, it should revise entries on Address table and make corrections if required.
Here is the SQL trigger for Correction table
CREATE TRIGGER ReviewEntriesOnAddress ON Correction AFTER INSERT
AS
BEGIN
insert into Correction (
falseForm,
correctForm
)
select
distinct
d.correctForm,
i.correctForm
from inserted i
inner join deleted d
on i.CorrectionId = d.CorrectionId
delete Correction
from Correction c
inner join inserted i
on c.correctForm = i.correctForm
where
c.correctForm = c.falseForm
update Address
set
City = i.correctForm
from Correction i
inner join Address a
on a.City = i.falseForm
END
And now SQL programmers can execute following SQL script to test the latest SQL trigger
insert into Correction values (N'Phyladelphia', N'Philadelphia')
select * from Address
And here we have again correct entries on Address table.
