12강: 문자열 함수

-- Build a contact info string with ||
SELECT
    name,
    phone || ' — ' || email AS contact_info
FROM customers
LIMIT 5;

-- Build a contact info string with CONCAT()
SELECT
    name,
    CONCAT(phone, ' — ', email) AS contact_info
FROM customers
LIMIT 5;

-- Display SKU with category prefix
SELECT
    p.name,
    cat.name || '-' || p.sku AS display_sku
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
LIMIT 5;

-- Display SKU with category prefix
SELECT
    p.name,
    CONCAT(cat.name, '-', p.sku) AS display_sku
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
LIMIT 5;

-- Users per email domain (after @)
SELECT DISTINCT
    SUBSTR(email, INSTR(email, '@') + 1) AS domain,
    COUNT(*) AS users
FROM customers
GROUP BY domain
ORDER BY users DESC
LIMIT 5;

-- Users per email domain (after @)
SELECT
    SUBSTRING(email, LOCATE('@', email) + 1) AS domain,
    COUNT(*) AS users
FROM customers
GROUP BY domain
ORDER BY users DESC
LIMIT 5;

-- Users per email domain (after @)
SELECT
    SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain,
    COUNT(*) AS users
FROM customers
GROUP BY domain
ORDER BY users DESC
LIMIT 5;

SELECT
    name || ' (' || COALESCE(birth_date, '미입력') || ')' AS contact_info
FROM customers
LIMIT 5;

-- MySQL의 CONCAT()은 NULL이 있어도 NULL을 반환
SELECT
    CONCAT(name, ' (', COALESCE(birth_date, '미입력'), ')') AS contact_info
FROM customers
LIMIT 5;

-- CONCAT_WS()는 NULL을 건너뜀 (구분자 지정)
SELECT
    CONCAT_WS(' | ', name, phone, email) AS contact_info
FROM customers
LIMIT 5;

-- 고객 ID를 5자리 0-패딩
SELECT
    id,
    printf('%05d', id) AS padded_id,
    name
FROM customers
LIMIT 5;

-- 고객 ID를 5자리 0-패딩
SELECT
    id,
    LPAD(id, 5, '0') AS padded_id,
    name
FROM customers
LIMIT 5;

-- 상품명을 30자 고정폭으로 (오른쪽 공백 채움)
SELECT RPAD(name, 30, ' ') AS fixed_name, price
FROM products
LIMIT 5;

-- 고객 ID를 5자리 0-패딩
SELECT
    id,
    LPAD(id::text, 5, '0') AS padded_id,
    name
FROM customers
LIMIT 5;

-- 상품명을 30자 고정폭으로
SELECT RPAD(name, 30, ' ') AS fixed_name, price
FROM products
LIMIT 5;

-- 공급업체별 상품명 목록
SELECT
    s.company_name,
    GROUP_CONCAT(p.name, ', ') AS product_list,
    COUNT(*)                   AS product_count
FROM products AS p
INNER JOIN suppliers AS s ON p.supplier_id = s.id
WHERE p.is_active = 1
GROUP BY s.company_name
ORDER BY product_count DESC
LIMIT 3;

-- 공급업체별 상품명 목록
SELECT
    s.company_name,
    GROUP_CONCAT(p.name ORDER BY p.name SEPARATOR ', ') AS product_list,
    COUNT(*) AS product_count
FROM products AS p
INNER JOIN suppliers AS s ON p.supplier_id = s.id
WHERE p.is_active = 1
GROUP BY s.company_name
ORDER BY product_count DESC
LIMIT 3;

-- 공급업체별 상품명 목록
SELECT
    s.company_name,
    STRING_AGG(p.name, ', ' ORDER BY p.name) AS product_list,
    COUNT(*) AS product_count
FROM products AS p
INNER JOIN suppliers AS s ON p.supplier_id = s.id
WHERE p.is_active = 1
GROUP BY s.company_name
ORDER BY product_count DESC
LIMIT 3;

-- 상품명에 숫자가 포함된 상품 (GLOB 사용)
SELECT name, price
FROM products
WHERE name GLOB '*[0-9]*'
LIMIT 5;

-- 대문자 영문으로 시작하는 상품
SELECT name
FROM products
WHERE name GLOB '[A-Z]*'
LIMIT 5;

-- 모델 번호 패턴: 영문자 뒤에 숫자가 오는 상품 (예: RTX 5070, DDR5 16GB)
SELECT name, price
FROM products
WHERE name REGEXP '[A-Za-z]+[0-9]+'
LIMIT 5;

-- 이메일 기본 형식 검증 (@ 앞뒤로 문자가 있는지)
SELECT name, email
FROM customers
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'
LIMIT 5;

-- 모델 번호 패턴: 영문자 뒤에 숫자가 오는 상품
SELECT name, price
FROM products
WHERE name ~ '[A-Za-z]+[0-9]+'
LIMIT 5;

-- 대소문자 무시: 'gaming' 포함 상품 (~* 사용)
SELECT name, price
FROM products
WHERE name ~* 'gaming'
LIMIT 5;

-- 이메일 기본 형식 검증
SELECT name, email
FROM customers
WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
LIMIT 5;