1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
INSERT INTO dtf.madelines_contacts
SELECT
id,
upper(split_part(regexp_replace(trim(full_name), '([^[[:ascii:]]]*)|([[:digit:]]*)', '', 'g'), ' ', 1))::public.prefix AS title,
initcap(split_part(regexp_replace(trim(full_name), '([^[[:ascii:]]]*)|([[:digit:]]*)', '', 'g'), ' ', 2)) AS first_name,
initcap(split_part(regexp_replace(trim(full_name), '([^[[:ascii:]]]*)|([[:digit:]]*)', '', 'g'), ' ', 3)) AS last_name,
concat(
lpad(
regexp_replace(
regexp_replace(phone, '[^0-9\.\-]', '', 'g'),
'(.?.?.?)[\.\-].*',
'\1',
'g'
),
3, '0'
),
'.',
lpad(
regexp_replace(
regexp_replace(phone, '[^0-9\.\-]', '', 'g'),
'.*[\.\-](.?.?.?)[\.\-].*',
'\1',
'g'
),
3, '0'
),
'.',
lpad(
regexp_replace(
regexp_replace(phone, '[^0-9\.\-]', '', 'g'),
'.*[\.\-](.?.?.?)',
'\1',
'g'
),
3, '0'
)
) AS phone,
regexp_replace(trim(email), '[^[[:ascii:]]]', '', 'g') AS email,
trim(favorite) LIKE '1' AS favorite,
created_at::timestamp AS created_at
FROM dtf.madelines_contacts_corrupted AS c
|
