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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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

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
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.

SQL Server trigger for misspelled words correction







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums






SQL Split String Related SQL Server Tutorials

Split String using XML - How to Convert or Split a Delimited String Values to Rows using T-SQL XML Commands
Split String Into Fixed Length Pieces in SQL
Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function
TSQL Character Split Function in SQL Server
Case Sensitive SQL Split Function
SQL Server 2016 Split String Function STRING_SPLIT
SQL Server String Split T-SQL CLR Function Sample
SQL Server CLR Split String Function for 2-Dimensional Array
T-SQL Split User Defined Function


Copyright © 2004 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems