문자열/숫자 함수

SELECT
    name,
    LENGTH(name) AS name_length
FROM products
ORDER BY name_length DESC
LIMIT 10;

SELECT DISTINCT UPPER(brand) AS brand_upper
FROM products
ORDER BY brand_upper;

SELECT
    brand,
    name,
    SUBSTR(name, LENGTH(brand) + 2) AS model_name
FROM products
WHERE name LIKE brand || ' %'
ORDER BY brand, model_name
LIMIT 15;

SELECT
    email,
    SUBSTR(email, 1, INSTR(email, '@') - 1) AS user_id
FROM customers
ORDER BY user_id
LIMIT 15;

SELECT
    name,
    TRIM(REPLACE(REPLACE(REPLACE(name, '블랙', ''), '화이트', ''), '실버', '')) AS name_no_color
FROM products
WHERE name LIKE '%블랙%' OR name LIKE '%화이트%' OR name LIKE '%실버%'
ORDER BY name
LIMIT 15;

SELECT
    name,
    phone,
    REPLACE(phone, '-', '') AS phone_no_dash,
    LENGTH(REPLACE(phone, '-', '')) AS digit_count
FROM customers
LIMIT 10;

SELECT
    sku,
    SUBSTR(sku, 1, 2) AS category_code,
    name
FROM products
ORDER BY category_code, sku
LIMIT 15;

SELECT
    cat.name AS category,
    COUNT(DISTINCT p.brand) AS brand_count,
    GROUP_CONCAT(DISTINCT p.brand, ', ') AS brands
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE p.is_active = 1
GROUP BY cat.id, cat.name
ORDER BY brand_count DESC
LIMIT 10;

SELECT
    name,
    SUBSTR(name, INSTR(name, 'RTX') + 4, 4) AS rtx_model
FROM products
WHERE name LIKE '%RTX%'
ORDER BY rtx_model DESC;

SELECT
    order_number,
    total_amount,
    ROUND(total_amount, -4) AS rounded_10k,
    ROUND(total_amount, -4) / 10000 AS in_man_won
FROM orders
WHERE status NOT IN ('cancelled')
ORDER BY total_amount DESC
LIMIT 10;

SELECT
    name,
    price,
    cost_price,
    ROUND(100.0 * (price - cost_price) / NULLIF(cost_price, 0), 1) AS margin_pct
FROM products
WHERE is_active = 1
ORDER BY margin_pct DESC NULLS LAST
LIMIT 15;

SELECT
    order_number,
    total_amount,
    printf('%,d', CAST(total_amount AS INTEGER)) || '원' AS formatted_amount
FROM orders
WHERE status NOT IN ('cancelled')
ORDER BY total_amount DESC
LIMIT 10;

SELECT
    name,
    price,
    CASE
        WHEN price < 100000 THEN '10만원 미만'
        WHEN price < 500000 THEN '10~50만원'
        WHEN price < 1000000 THEN '50~100만원'
        WHEN price < 2000000 THEN '100~200만원'
        ELSE '200만원 이상'
    END AS price_range,
    IIF(price >= 1000000, '고가', '일반') AS price_class
FROM products
WHERE is_active = 1
ORDER BY price DESC
LIMIT 15;

SELECT
    name,
    stock_qty,
    TYPEOF(stock_qty) AS original_type,
    CAST(stock_qty AS REAL) AS stock_as_real,
    TYPEOF(CAST(stock_qty AS REAL)) AS converted_type,
    CAST(price AS INTEGER) AS price_int
FROM products
WHERE is_active = 1
ORDER BY stock_qty DESC
LIMIT 10;

SELECT
    grade,
    COUNT(*) AS total_count,
    GROUP_CONCAT(name, ', ') AS sample_names
FROM (
    SELECT
        name,
        grade,
        ROW_NUMBER() OVER (PARTITION BY grade ORDER BY created_at) AS rn
    FROM customers
    WHERE is_active = 1
)
WHERE rn <= 5
GROUP BY grade
ORDER BY total_count DESC;

SELECT
    order_number,
    SUBSTR(order_number, 5, 8) AS date_part,
    SUBSTR(order_number, 5, 4) || '-' || SUBSTR(order_number, 9, 2) || '-' || SUBSTR(order_number, 11, 2) AS formatted_date,
    CAST(SUBSTR(order_number, 14) AS INTEGER) AS sequence_no
FROM orders
ORDER BY ordered_at DESC
LIMIT 10;

SELECT name, price, brand
FROM products
WHERE name GLOB '*[0-9]GB*'
   OR name GLOB '*[0-9]TB*'
   OR name GLOB '*[0-9]MHz*'
   OR name GLOB '*[0-9]W*'
ORDER BY price DESC
LIMIT 15;

SELECT
    '[' || brand || '] ' || name
    || ' | ' || printf('%,d', CAST(price AS INTEGER)) || '원'
    || ' | 재고: ' || stock_qty || '개'
    || ' | ' || COALESCE('무게: ' || (weight_grams / 1000.0) || 'kg', '무게 미정')
    AS product_card
FROM products
WHERE is_active = 1
ORDER BY price DESC
LIMIT 5;

SELECT
    COALESCE(acquisition_channel, '미확인') AS channel,
    SUBSTR(email, INSTR(email, '@') + 1) AS domain,
    COUNT(*) AS customer_count
FROM customers
GROUP BY COALESCE(acquisition_channel, '미확인'),
         SUBSTR(email, INSTR(email, '@') + 1)
ORDER BY customer_count DESC
LIMIT 10;

SELECT
    name,
    CASE
        WHEN price < 100000 THEN '저가'
        WHEN price < 500000 THEN '중가'
        WHEN price < 1000000 THEN '중고가'
        ELSE '고가'
    END AS price_tier,
    printf('%.1f%%', 100.0 * (price - cost_price) / NULLIF(cost_price, 0)) AS margin_pct,
    CASE
        WHEN stock_qty = 0 THEN '품절'
        WHEN stock_qty < 10 THEN '부족'
        WHEN stock_qty < 50 THEN '보통'
        ELSE '충분'
    END AS stock_status,
    IIF(is_active = 1, '판매중', '단종') AS sale_status,
    printf('%,d원', CAST(price AS INTEGER)) AS display_price
FROM products
ORDER BY price DESC
LIMIT 15;