SQL string functions: TRIM, SUBSTRING, REPLACE and regex

SELECT description,
       position('HEART' IN description) AS pos,
       left(description, 5) AS first5,
       right(description, 6) AS last6,
       substring(description FROM 7 FOR 7) AS from7
FROM (VALUES ('WHITE HANGING HEART T-LIGHT HOLDER'),
             ('JUMBO BAG RED RETROSPOT')) AS t(description);

            description             | pos | first5 | last6  |  from7  
------------------------------------+-----+--------+--------+---------
 WHITE HANGING HEART T-LIGHT HOLDER |  15 | WHITE  | HOLDER | HANGING
 JUMBO BAG RED RETROSPOT            |   0 | JUMBO  | ROSPOT | BAG RED
(2 rows)

SELECT count(DISTINCT description) AS descriptions,
       count(DISTINCT description) FILTER (WHERE description <> trim(description)) AS untrimmed,
       count(DISTINCT description) FILTER (WHERE description LIKE '%  %') AS double_spaces,
       count(DISTINCT description) FILTER (WHERE description <> upper(description)) AS not_upper
FROM raw_retail;

SELECT DISTINCT stock_code, '[' || description || ']' AS shown_with_brackets
FROM raw_retail
WHERE stock_code IN ('21243', '22900')
ORDER BY stock_code, shown_with_brackets;

 descriptions | untrimmed | double_spaces | not_upper 
--------------+-----------+---------------+-----------
         4223 |       757 |           191 |       201
(1 row)
 
 stock_code |        shown_with_brackets         
------------+------------------------------------
 21243      | [PINK  POLKADOT PLATE ]
 21243      | [PINK POLKADOT PLATE ]
 22900      | [ SET 2 TEA TOWELS I LOVE LONDON ]
 22900      | [SET 2 TEA TOWELS I LOVE LONDON ]
(4 rows)

WITH v AS (
  SELECT DISTINCT stock_code,
         description,
         upper(regexp_replace(trim(description), '\s+', ' ', 'g')) AS clean
  FROM raw_retail
  WHERE description IS NOT NULL
)
SELECT count(DISTINCT description) AS raw_descriptions,
       count(DISTINCT clean) AS clean_descriptions
FROM v;

 raw_descriptions | clean_descriptions 
------------------+--------------------
             4223 |               4186
(1 row)

WITH v AS (
  SELECT DISTINCT upper(stock_code) AS code,
         description,
         upper(regexp_replace(trim(description), '\s+', ' ', 'g')) AS clean
  FROM raw_retail
  WHERE description IS NOT NULL AND unit_price > 0 AND stock_code ~ '^\d{5}'
),
per_code AS (
  SELECT code,
         count(DISTINCT description) AS raw_n,
         count(DISTINCT clean) AS clean_n
  FROM v
  GROUP BY code
)
SELECT count(*) FILTER (WHERE raw_n > 1) AS codes_with_several_raw,
       count(*) FILTER (WHERE clean_n > 1) AS codes_with_several_clean
FROM per_code;

 codes_with_several_raw | codes_with_several_clean 
------------------------+--------------------------
                    215 |                      207
(1 row)

CREATE TABLE products_clean AS
SELECT DISTINCT ON (upper(stock_code))
       upper(stock_code) AS stock_code,
       upper(regexp_replace(trim(description), '\s+', ' ', 'g')) AS description
FROM raw_retail
WHERE description IS NOT NULL AND unit_price > 0 AND stock_code ~ '^\d{5}'
GROUP BY upper(stock_code), description
ORDER BY upper(stock_code), count(*) DESC, description;

SELECT * FROM products_clean
WHERE stock_code IN ('21243', '22900', '23236');

SELECT 3805
 stock_code |          description           
------------+--------------------------------
 21243      | PINK POLKADOT PLATE
 22900      | SET 2 TEA TOWELS I LOVE LONDON
 23236      | STORAGE TIN VINTAGE DOILY
(3 rows)

SELECT stock_code,
       description,
       length(description) AS chars,
       octet_length(description) AS bytes,
       substring(description FROM '£(\d+\.\d{2})')::numeric AS voucher_value,
       split_part(stock_code, '_', 3) AS code_part
FROM raw_retail
WHERE description LIKE '%£%'
GROUP BY stock_code, description
ORDER BY voucher_value;

  stock_code  |             description             | chars | bytes | voucher_value | code_part 
--------------+-------------------------------------+-------+-------+---------------+-----------
 gift_0001_10 | Dotcomgiftshop Gift Voucher £10.00  |    34 |    35 |         10.00 | 10
 gift_0001_20 | Dotcomgiftshop Gift Voucher £20.00  |    34 |    35 |         20.00 | 20
 gift_0001_30 | Dotcomgiftshop Gift Voucher £30.00  |    34 |    35 |         30.00 | 30
 gift_0001_40 | Dotcomgiftshop Gift Voucher £40.00  |    34 |    35 |         40.00 | 40
 gift_0001_50 | Dotcomgiftshop Gift Voucher £50.00  |    34 |    35 |         50.00 | 50
 22016        | Dotcomgiftshop Gift Voucher £100.00 |    35 |    36 |        100.00 | 
(6 rows)

SELECT length('SALE' || chr(160)) AS with_nbsp,
       length(trim('SALE' || chr(160))) AS after_trim,
       length(trim(BOTH ' ' || chr(160) FROM 'SALE' || chr(160))) AS trim_listed_chars,
       length(regexp_replace('SALE' || chr(160), '[\s ]+$', '')) AS after_regex;

 with_nbsp | after_trim | trim_listed_chars | after_regex 
-----------+------------+-------------------+-------------
         5 |          5 |                 4 |           4
(1 row)

SELECT count(DISTINCT stock_code) AS codes,
       count(DISTINCT upper(stock_code)) AS codes_ignoring_case
FROM raw_retail;

SELECT stock_code, count(*) AS lines
FROM raw_retail
WHERE upper(stock_code) = '15056BL'
GROUP BY stock_code;

 codes | codes_ignoring_case 
-------+---------------------
  4070 |                3958
(1 row)
 
 stock_code | lines 
------------+-------
 15056BL    |   326
 15056bl    |    62
(2 rows)

SELECT stock_code,
       substring(stock_code FROM '^\d{5}') AS base_code,
       upper(substring(stock_code FROM '^\d{5}([A-Za-z]+)$')) AS variant
FROM (VALUES ('85123A'), ('15056bl'), ('22423'), ('DCGS0066P'), ('POST')) AS t(stock_code);

 stock_code | base_code | variant 
------------+-----------+---------
 85123A     | 85123     | A
 15056bl    | 15056     | BL
 22423      | 22423     | 
 DCGS0066P  |           | 
 POST       |           | 
(5 rows)

WITH parts AS (
  SELECT DISTINCT upper(stock_code) AS code,
         substring(stock_code FROM '^\d{5}') AS base_code,
         upper(substring(stock_code FROM '^\d{5}([A-Za-z]+)$')) AS variant
  FROM raw_retail
)
SELECT CASE WHEN base_code IS NULL THEN 'not a product code'
            WHEN variant IS NULL THEN 'plain 5 digits'
            ELSE 'with variant letters' END AS kind,
       count(*) AS codes
FROM parts
GROUP BY 1
ORDER BY codes DESC;

WITH parts AS (
  SELECT DISTINCT upper(stock_code) AS code,
         substring(stock_code FROM '^\d{5}') AS base_code
  FROM raw_retail
)
SELECT base_code, count(*) AS variants, string_agg(code, ', ' ORDER BY code) AS codes
FROM parts
WHERE base_code IS NOT NULL
GROUP BY base_code
ORDER BY variants DESC, base_code
LIMIT 3;

         kind         | codes 
----------------------+-------
 plain 5 digits       |  2946
 with variant letters |   980
 not a product code   |    32
(3 rows)
 
 base_code | variants |                                                                                             codes                                                                                              
-----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 90214     |       24 | 90214A, 90214B, 90214C, 90214D, 90214E, 90214F, 90214G, 90214H, 90214I, 90214J, 90214K, 90214L, 90214M, 90214N, 90214O, 90214P, 90214R, 90214S, 90214T, 90214U, 90214V, 90214W, 90214Y, 90214Z
 85049     |        8 | 85049A, 85049B, 85049C, 85049D, 85049E, 85049F, 85049G, 85049H
 79323     |        7 | 79323B, 79323G, 79323GR, 79323LP, 79323P, 79323S, 79323W
(3 rows)

SELECT DISTINCT ON (invoice_no)
       invoice_no,
       customer_id,
       'Customer ' || customer_id AS with_pipes,
       concat('Customer ', customer_id) AS with_concat,
       concat_ws(' / ', invoice_no, customer_id, country) AS with_concat_ws
FROM raw_retail
WHERE invoice_no IN ('536365', '536414')
ORDER BY invoice_no;

 invoice_no | customer_id |   with_pipes   |  with_concat   |         with_concat_ws          
------------+-------------+----------------+----------------+---------------------------------
 536365     |       17850 | Customer 17850 | Customer 17850 | 536365 / 17850 / United Kingdom
 536414     |             |                | Customer       | 536414 / United Kingdom
(2 rows)

WITH bought AS (
  SELECT DISTINCT r.customer_id, p.description
  FROM raw_retail AS r
  JOIN products_clean AS p ON p.stock_code = upper(r.stock_code)
  WHERE r.customer_id IS NOT NULL AND r.quantity > 0
)
SELECT customer_id,
       count(*) AS products,
       string_agg(description, ', ' ORDER BY description) AS product_list
FROM bought
WHERE customer_id = 18280
GROUP BY customer_id;

 customer_id | products |                                                                                                                               product_list                                                                                                                                
-------------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       18280 |       10 | ALARM CLOCK BAKELIKE CHOCOLATE, ALARM CLOCK BAKELIKE RED, GUMBALL COAT RACK, KINGS CHOICE TEA CADDY, PAPER CHAIN KIT EMPIRE, RETROSPOT LAMP, SET OF 2 ROUND TINS CAMEMBERT, VINTAGE UNION JACK SHOPPING BAG, WOOD BLACK BOARD ANT WHITE FINISH, WOODEN UNION JACK BUNTING
(1 row)

WITH bought AS (
  SELECT DISTINCT r.customer_id, p.description
  FROM raw_retail AS r
  JOIN products_clean AS p ON p.stock_code = upper(r.stock_code)
  WHERE r.customer_id IS NOT NULL AND r.quantity > 0
),
lists AS (
  SELECT customer_id,
         string_agg(description, ', ' ORDER BY description) AS product_list
  FROM bought
  GROUP BY customer_id
)
SELECT count(*) AS customers,
       count(*) FILTER (WHERE length(product_list) > 1024) AS longer_than_1024,
       max(length(product_list)) AS longest
FROM lists;

 customers | longer_than_1024 | longest 
-----------+------------------+---------
      4335 |             2137 |   51082
(1 row)