Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Removing Unwanted Characters from Data - SQL Server

Problem Statement¶

In a text field in a database table, we have phone numbers that have been entered with inconsistent formatting. To restore consistent formatting, we would like to remove all non-numeric characters from the data.

Solution¶

• Create a view that contains 245 rows (one for each ASCII code, except for those representing the digits '0' through '9') returning a single text field containing a single character.
  

• Create a user-defined function that will use the above view to remove non-numeric characters from submitted text. This is simply accomplished via the following form: select @phone = replace(@phone, MyChar, '') from dbo.NonNumericCharacters
  

• Apply the above user-defined function to all rows of the table containing the database column to be cleaned.
  

T-SQL Code¶

--=================================================================================================
--  Contacts Table
--  This is the table containing the data to be cleaned (in the Phone column)
---------------------------------------------------------------------------------------------------
if exists (select 2 from sys.objects where name='Contacts')
    drop table dbo.Contacts
go

CREATE TABLE [dbo].[Contacts](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Phone] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--=================================================================================================
--  dbo.AllCharacters view
--  This view supports the cleaning of the phone number data
---------------------------------------------------------------------------------------------------
if exists (select 2 from sys.objects where name = 'AllCharacters')
    drop view dbo.AllCharacters
	
go

create view [dbo].[AllCharacters] as 

select mychar = char(0)
union select mychar = char(1)
union select mychar = char(2)
union select mychar = char(2)
union select mychar = char(3)
union select mychar = char(4)
union select mychar = char(5)
union select mychar = char(6)
union select mychar = char(7)
union select mychar = char(8)
union select mychar = char(9)
union select mychar = char(10)
union select mychar = char(11)
union select mychar = char(12)
union select mychar = char(13)
union select mychar = char(14)
union select mychar = char(15)
union select mychar = char(16)
union select mychar = char(17)
union select mychar = char(18)
union select mychar = char(19)
union select mychar = char(20)
union select mychar = char(21)
union select mychar = char(22)
union select mychar = char(23)
union select mychar = char(24)
union select mychar = char(25)
union select mychar = char(26)
union select mychar = char(27)
union select mychar = char(28)
union select mychar = char(29)
union select mychar = char(30)
union select mychar = char(31)
union select mychar = char(32)
union select mychar = char(33)
union select mychar = char(34)
union select mychar = char(35)
union select mychar = char(36)
union select mychar = char(37)
union select mychar = char(38)
union select mychar = char(39)
union select mychar = char(40)
union select mychar = char(41)
union select mychar = char(42)
union select mychar = char(43)
union select mychar = char(44)
union select mychar = char(45)
union select mychar = char(46)
union select mychar = char(47)
union select mychar = char(48)
union select mychar = char(49)
union select mychar = char(50)
union select mychar = char(51)
union select mychar = char(52)
union select mychar = char(53)
union select mychar = char(54)
union select mychar = char(55)
union select mychar = char(56)
union select mychar = char(57)
union select mychar = char(58)
union select mychar = char(59)
union select mychar = char(60)
union select mychar = char(61)
union select mychar = char(62)
union select mychar = char(63)
union select mychar = char(64)
union select mychar = char(65)
union select mychar = char(66)
union select mychar = char(67)
union select mychar = char(68)
union select mychar = char(69)
union select mychar = char(70)
union select mychar = char(71)
union select mychar = char(72)
union select mychar = char(73)
union select mychar = char(74)
union select mychar = char(75)
union select mychar = char(76)
union select mychar = char(77)
union select mychar = char(78)
union select mychar = char(79)
union select mychar = char(80)
union select mychar = char(81)
union select mychar = char(82)
union select mychar = char(83)
union select mychar = char(84)
union select mychar = char(85)
union select mychar = char(86)
union select mychar = char(87)
union select mychar = char(88)
union select mychar = char(89)
union select mychar = char(90)
union select mychar = char(91)
union select mychar = char(92)
union select mychar = char(93)
union select mychar = char(94)
union select mychar = char(95)
union select mychar = char(96)
union select mychar = char(97)
union select mychar = char(98)
union select mychar = char(99)
union select mychar = char(100)
union select mychar = char(101)
union select mychar = char(102)
union select mychar = char(102)
union select mychar = char(103)
union select mychar = char(104)
union select mychar = char(105)
union select mychar = char(106)
union select mychar = char(107)
union select mychar = char(108)
union select mychar = char(109)
union select mychar = char(110)
union select mychar = char(111)
union select mychar = char(112)
union select mychar = char(113)
union select mychar = char(114)
union select mychar = char(115)
union select mychar = char(116)
union select mychar = char(117)
union select mychar = char(118)
union select mychar = char(119)
union select mychar = char(120)
union select mychar = char(121)
union select mychar = char(122)
union select mychar = char(123)
union select mychar = char(124)
union select mychar = char(125)
union select mychar = char(126)
union select mychar = char(127)
union select mychar = char(128)
union select mychar = char(129)
union select mychar = char(130)
union select mychar = char(131)
union select mychar = char(132)
union select mychar = char(133)
union select mychar = char(134)
union select mychar = char(135)
union select mychar = char(136)
union select mychar = char(137)
union select mychar = char(138)
union select mychar = char(139)
union select mychar = char(140)
union select mychar = char(141)
union select mychar = char(142)
union select mychar = char(143)
union select mychar = char(144)
union select mychar = char(145)
union select mychar = char(146)
union select mychar = char(147)
union select mychar = char(148)
union select mychar = char(149)
union select mychar = char(150)
union select mychar = char(151)
union select mychar = char(152)
union select mychar = char(153)
union select mychar = char(154)
union select mychar = char(155)
union select mychar = char(156)
union select mychar = char(157)
union select mychar = char(158)
union select mychar = char(159)
union select mychar = char(160)
union select mychar = char(161)
union select mychar = char(162)
union select mychar = char(163)
union select mychar = char(164)
union select mychar = char(165)
union select mychar = char(166)
union select mychar = char(167)
union select mychar = char(168)
union select mychar = char(169)
union select mychar = char(170)
union select mychar = char(171)
union select mychar = char(172)
union select mychar = char(173)
union select mychar = char(174)
union select mychar = char(175)
union select mychar = char(176)
union select mychar = char(177)
union select mychar = char(178)
union select mychar = char(179)
union select mychar = char(180)
union select mychar = char(181)
union select mychar = char(182)
union select mychar = char(183)
union select mychar = char(184)
union select mychar = char(185)
union select mychar = char(186)
union select mychar = char(187)
union select mychar = char(188)
union select mychar = char(189)
union select mychar = char(190)
union select mychar = char(191)
union select mychar = char(192)
union select mychar = char(193)
union select mychar = char(194)
union select mychar = char(195)
union select mychar = char(196)
union select mychar = char(197)
union select mychar = char(198)
union select mychar = char(199)
union select mychar = char(200)
union select mychar = char(201)
union select mychar = char(202)
union select mychar = char(202)
union select mychar = char(203)
union select mychar = char(204)
union select mychar = char(205)
union select mychar = char(206)
union select mychar = char(207)
union select mychar = char(208)
union select mychar = char(209)
union select mychar = char(210)
union select mychar = char(211)
union select mychar = char(212)
union select mychar = char(213)
union select mychar = char(214)
union select mychar = char(215)
union select mychar = char(216)
union select mychar = char(217)
union select mychar = char(218)
union select mychar = char(219)
union select mychar = char(220)
union select mychar = char(221)
union select mychar = char(222)
union select mychar = char(223)
union select mychar = char(224)
union select mychar = char(225)
union select mychar = char(226)
union select mychar = char(227)
union select mychar = char(228)
union select mychar = char(229)
union select mychar = char(230)
union select mychar = char(231)
union select mychar = char(232)
union select mychar = char(233)
union select mychar = char(234)
union select mychar = char(235)
union select mychar = char(236)
union select mychar = char(237)
union select mychar = char(238)
union select mychar = char(239)
union select mychar = char(240)
union select mychar = char(241)
union select mychar = char(242)
union select mychar = char(243)
union select mychar = char(244)
union select mychar = char(245)
union select mychar = char(246)
union select mychar = char(247)
union select mychar = char(248)
union select mychar = char(249)
union select mychar = char(250)
union select mychar = char(251)
union select mychar = char(252)
union select mychar = char(253)
union select mychar = char(254)
union select mychar = char(255)

GO

--=================================================================================================
--  dbo.NonNumericCharacters view
--  This view supports the cleaning of the phone number data
---------------------------------------------------------------------------------------------------
if exists (select 2 from sys.objects where name = 'NonNumericCharacters')
    drop view dbo.NonNumericCharacters
	
go

create view [dbo].[NonNumericCharacters] as 

select * 
from dbo.AllCharacters
where MyChar not in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
GO

--=================================================================================================
--  CleanPhoneNumber function
--  This function removes all non-numeric characters from the input text
---------------------------------------------------------------------------------------------------
if exists (select 2 from sys.objects where name='CleanPhoneNumber')
    drop function dbo.CleanPhoneNumber
go
create function dbo.CleanPhoneNumber(@phone varchar(50)) returns varchar(50) as begin

select @phone = replace(@phone, MyChar, '')
from dbo.NonNumericCharacters

return @phone

end
go
--=================================================================================================
--  Test Script
--  This test verifies that all non-numeric characters were removed from the Contacts.Phone column
---------------------------------------------------------------------------------------------------
delete from Contacts
insert into Contacts (Name, Phone) values ('John Doe','Cell# 404-555-1212')
insert into Contacts (Name, Phone) values ('Jane Doe','WK (678) 555-1234')
insert into Contacts (Name, Phone) values ('John Brown','770.555.5678')

update dbo.Contacts
set Phone = dbo.CleanPhoneNumber(Phone)

select * from dbo.Contacts