Python3_workout/7-2.sql at master · Manix049/Python3_workout · GitHub

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
-- build customer key
select
    lower(name),
    regexp_replace(name, '[^a-zA-Z0-9]+', ''),
    lower(regexp_replace(name, '[^a-zA-Z0-9]+', '')) as CustKey
from clients

-- parse out the quote from the url
select
    name,
    summaryquote,
    parse_url(summaryquote,'PATH'),
    locate('/',parse_url(summaryquote,'PATH'),2),
    substring(parse_url(summaryquote,'PATH'),9,4),
    substring(
        parse_url(summaryquote,'PATH'),
        locate('/',parse_url(summaryquote,'PATH'),2)+1
        ,4
    ) as symbol
from clients

-- combine the two for a new customer key
select
    name,
    concat(
        lower(regexp_replace(name, '[^a-zA-Z0-9]+', '')),
        '-',
        substring(parse_url(summaryquote,'PATH'),9,4)
    ) as CustKey
from clients