Slide 25
Slide 25 text
Cleansing Data - SQL
DELIMITER
DELIMITER $$
$$
CREATE
CREATE FUNCTION
FUNCTION CleanPhoneNumbers_sql
CleanPhoneNumbers_sql(
(phone_in
phone_in VARCHAR
VARCHAR(
(50
50)
))
)
RETURNS
RETURNS CHAR
CHAR(
(13
13)
) DETERMINISTIC
DETERMINISTIC
BEGIN
BEGIN
DECLARE
DECLARE cleaned
cleaned VARCHAR
VARCHAR(
(50
50)
);
;
-- Replace the leading '+' with '00'
-- Replace the leading '+' with '00'
SET
SET cleaned
cleaned =
= IF
IF(
(LEFT
LEFT(
(phone_in
phone_in,
, 1
1)
) =
= '+'
'+',
, CONCAT
CONCAT(
('00'
'00',
, SUBSTRING
SUBSTRING(
(phone_in
phone_in,
, 2
2)
))
),
, phone_in
phone_in)
);
;
-- Remove all non-numeric characters
-- Remove all non-numeric characters
SET
SET cleaned
cleaned =
= REPLACE
REPLACE(
(REPLACE
REPLACE(
(REPLACE
REPLACE(
(REPLACE
REPLACE(
(REPLACE
REPLACE(
(REPLACE
REPLACE(
(REPLACE
REPLACE(
(REPLACE
REPLACE(
(REPLACE
REPLACE(
(REPLACE
REPLACE(
(
cleaned
cleaned,
, '-'
'-',
, ''
'')
),
, '('
'(',
, ''
'')
),
, ')'
')',
, ''
'')
),
, ' '
' ',
, ''
'')
),
, '.'
'.',
, ''
'')
),
, '+'
'+',
, ''
'')
),
, ','
',',
, ''
'')
),
, '/'
'/',
, ''
'')
),
, '\\'
'\\',
, ''
'')
),
, ':'
':',
, ''
'')
);
;
-- Return the cleaned phone number if it's 10 or 13 digits, otherwise return NULL
-- Return the cleaned phone number if it's 10 or 13 digits, otherwise return NULL
IF
IF LENGTH
LENGTH(
(cleaned
cleaned)
) =
= 10
10 OR
OR LENGTH
LENGTH(
(cleaned
cleaned)
) =
= 13
13 THEN
THEN
RETURN
RETURN cleaned
cleaned;
;
ELSE
ELSE
RETURN
RETURN NULL
NULL;
;
END
END IF
IF;
;
END
END $$
$$
DELIMITER
DELIMITER ;
;
Copyright @ 2024 Oracle and/or its affiliates.
22