SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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)
create table Correction (
 CorrectionId int identity(1,1) not null,
 falseForm nvarchar(100),
 correctForm nvarchar(100),
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


update Address
 City = c.correctForm
from Inserted as i
inner join Correction c
 on i.City = c.falseForm
 Address.City = i.City


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

SQL Server database table trigger to correct spelling errors

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

test SQL trigger with multiple rows

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.

correct misspelled form or invalid typo using SQL trigger on SQL Server database

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

insert into Correction (
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
 c.correctForm = c.falseForm

update Address
 City = i.correctForm
from Correction i
inner join Address a
 on a.City = i.falseForm


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.

SQL Server trigger for misspelled words correction

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.