#ThanksOTN @OracleOTN Appreciation Day

Initial thanks to Oracle Technology Network and Oracle ACE Program for making us connect with Oracle community. And to Tim Hall (A.K.A. oracle-base) for setting all of us up for sharing some content with Oracle Community. So let’s start sharing my content. Click #ThanksOTN for all the tweets across OTN Appreciation Day.

As most of you know, I am a bit experienced in Oracle Data Integrator, but this time I will have more focus on Oracle Database, one of my favourite topics – especially when you are into text mining – which is Regular Expressions (REG_EXP).

Below code is very useful, especially when you are trying to eliminate some “unwanted” characters when you are digging into in-code data quality. Although, this approach looks like to have 2 different methods inside, you may replace unwanted characters into a space and eliminate 2 spaces as 1 space with “one” single SQL function.

REGEXP_REPLACE
(
translate
(
SOURCE_TABLE.SOURCE_COLUMN,’%&/0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz’||chr(1)||chr(2)||chr(3)||chr(4)||chr(5)||chr(6)||chr(7)||chr(8)||chr(9)||chr(10)||chr(11)||chr(12)||chr(13)||chr(14)||chr(15)||chr(16)||chr(17)||chr(18)||chr(19)||chr(20)||chr(21)||chr(22)||chr(23)||chr(24)||chr(25)||chr(26)||chr(27)||chr(28)||chr(29)||chr(30)||chr(31)||chr(32)||chr(33)||chr(34)||chr(35)||chr(36)||chr(39)||chr(40)||chr(41)||chr(42)||chr(43)||chr(44)||chr(46)||chr(58)||chr(59)||chr(60)||chr(61)||chr(62)||chr(63)||chr(91)||chr(92)||chr(93)||chr(94)||chr(96)||chr(123)||chr(124)||chr(125)||chr(126)||chr(127)||chr(128)||chr(129)||chr(130)||chr(131)||chr(132)||chr(133)||chr(134)||chr(135)||chr(136)||chr(137)||chr(138)||chr(139)||chr(140)||chr(141)||chr(142)||chr(143)||chr(144)||chr(145)||chr(146)||chr(147)||chr(148)||chr(149)||chr(150)||chr(151)||chr(152)||chr(153)||chr(154)||chr(155)||chr(156)||chr(157)||chr(158)||chr(159)||chr(160)||chr(161)||chr(162)||chr(163)||chr(164)||chr(165)||chr(166)||chr(167)||chr(168)||chr(169)||chr(170)||chr(171)||chr(172)||chr(173)||chr(174)||chr(175)||chr(176)||chr(177)||chr(178)||chr(179)||chr(180)||chr(181)||chr(182)||chr(183)||chr(184)||chr(185)||chr(186)||chr(187)||chr(188)||chr(189)||chr(190)||chr(191)||chr(192)||chr(193)||chr(194)||chr(195)||chr(196)||chr(197)||chr(198)||chr(199)||chr(200)||chr(201)||chr(202)||chr(203)||chr(204)||chr(205)||chr(206)||chr(207)||chr(209)||chr(210)||chr(211)||chr(212)||chr(213)||chr(215)||chr(216)||chr(217)||chr(218)||chr(219)||chr(223)||chr(224)||chr(225)||chr(226)||chr(227)||chr(228)||chr(229)||chr(230)||chr(231)||chr(232)||chr(233)||chr(234)||chr(235)||chr(236)||chr(237)||chr(238)||chr(239)||chr(240)||chr(241)||chr(242)||chr(243)||chr(244)||chr(245)||chr(246)||chr(247)||chr(248)||chr(249)||chr(250)||chr(251)||chr(252)||chr(253)||chr(255)
, ‘%&/0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz’), ‘( ){2,}’, ‘ ‘);