-- 去除字段左側(cè)的空格
SELECT 
    original_name,
    -- LTRIM函數(shù)只去除字符串左側(cè)的空格
    LTRIM(original_name) AS trimmed_left_name,
    -- 查看原始長(zhǎng)度和去除左側(cè)空格后的長(zhǎng)度對(duì)比
    LENGTH(original_name) AS original_length,
    LENGTH(LTRIM(original_name)) AS left_trimmed_length
FROM customer_data
WHERE original_name LIKE ' %'; -- 篩選出左側(cè)有空格的記錄

-- 結(jié)合其他函數(shù)使用
SELECT 
    first_name,
    last_name,
    -- 先去除左側(cè)空格，再進(jìn)行拼接
    CONCAT(LTRIM(first_name), ' ', LTRIM(last_name)) AS full_name_cleaned
FROM messy_customer_data;

-- 去除字段右側(cè)的空格
SELECT 
    product_code,
    description,
    -- RTRIM函數(shù)只去除字符串右側(cè)的空格
    RTRIM(product_code) AS clean_product_code,
    -- 去除描述字段右側(cè)的空格
    RTRIM(description) AS clean_description,
    -- 顯示處理前后的長(zhǎng)度差異
    LENGTH(product_code) AS original_code_length,
    LENGTH(RTRIM(product_code)) AS cleaned_code_length
FROM inventory
WHERE product_code LIKE '% '; -- 篩選出右側(cè)有空格的記錄

-- 在WHERE條件中使用RTRIM進(jìn)行精確匹配
SELECT *
FROM products
WHERE RTRIM(product_name) = 'iPhone 14 Pro';

-- 去除字段兩側(cè)的空格（最常用的方法）
SELECT 
    user_input,
    -- TRIM函數(shù)同時(shí)去除字符串前后的空格
    TRIM(user_input) AS clean_input,
    -- 顯示處理效果
    CONCAT('[', user_input, ']') AS original_with_brackets,
    CONCAT('[', TRIM(user_input), ']') AS cleaned_with_brackets
FROM form_submissions
WHERE user_input LIKE ' % ' OR user_input LIKE '% '; -- 包含前后空格的記錄

-- 更新表中數(shù)據(jù)，永久去除空格
UPDATE customer_addresses
SET 
    street_address = TRIM(street_address),
    city = TRIM(city),
    state = TRIM(state)
WHERE 
    street_address LIKE ' %' OR street_address LIKE '% ' OR
    city LIKE ' %' OR city LIKE '% ' OR
    state LIKE ' %' OR state LIKE '% ';

-- 使用標(biāo)準(zhǔn)SQL語(yǔ)法明確指定去除方向
SELECT 
    data_field,
    -- 去除兩側(cè)空格的標(biāo)準(zhǔn)語(yǔ)法
    TRIM(BOTH FROM data_field) AS both_sides_trimmed,
    -- 只去除前導(dǎo)空格的標(biāo)準(zhǔn)語(yǔ)法
    TRIM(LEADING FROM data_field) AS leading_trimmed,
    -- 只去除尾隨空格的標(biāo)準(zhǔn)語(yǔ)法
    TRIM(TRAILING FROM data_field) AS trailing_trimmed
FROM raw_data_table;

-- 使用TRIM去除自定義字符（部分?jǐn)?shù)據(jù)庫(kù)支持）
SELECT 
    phone_number,
    -- 去除電話號(hào)碼前后的連字符和空格
    TRIM(BOTH '-' FROM TRIM(phone_number)) AS clean_phone
FROM contact_info;

-- MySQL中的去空格方法
SELECT 
    text_column,
    -- 基本TRIM用法
    TRIM(text_column) AS standard_trim,
    -- 去除多個(gè)連續(xù)空格為單個(gè)空格（需結(jié)合其他函數(shù)）
    REGEXP_REPLACE(TRIM(text_column), '[[:space:]]+', ' ') AS single_spaces_only
FROM mysql_table;

-- SQL Server中的去空格方法
SELECT 
    text_field,
    -- 基本TRIM用法
    TRIM(text_field) AS standard_trim,
    -- LTRIM和RTRIM組合使用
    LTRIM(RTRIM(text_field)) AS combined_trim,
    -- 去除中間多余空格的復(fù)雜處理
    REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(text_field)), '  ', ' '), '  ', ' '), '  ', ' ') AS extra_spaces_removed
FROM sql_server_table;

-- Oracle中的去空格方法
SELECT 
    varchar_field,
    -- 標(biāo)準(zhǔn)TRIM用法
    TRIM(varchar_field) AS standard_trim,
    -- 使用REGEXP_REPLACE去除所有類型的空白字符
    REGEXP_REPLACE(varchar_field, '^[[:space:]]+|[[:space:]]+$', '') AS regex_trim
FROM oracle_table;

-- 數(shù)據(jù)清洗場(chǎng)景：清理導(dǎo)入的數(shù)據(jù)
SELECT 
    raw_customer_name,
    raw_email,
    raw_phone,
    -- 清理客戶姓名
    TRIM(raw_customer_name) AS clean_customer_name,
    -- 清理郵箱地址
    LOWER(TRIM(raw_email)) AS clean_email,
    -- 清理電話號(hào)碼（去除空格和連字符）
    REPLACE(REPLACE(TRIM(raw_phone), ' ', ''), '-', '') AS clean_phone
FROM imported_customer_data;

-- 查詢優(yōu)化場(chǎng)景：在WHERE子句中使用TRIM
SELECT *
FROM users
WHERE TRIM(username) = 'john_doe' -- 確保即使原數(shù)據(jù)有空格也能匹配
   OR TRIM(email) = 'john@example.com';

-- 報(bào)表生成場(chǎng)景：美化輸出格式
SELECT 
    employee_id,
    -- 確保員工姓名沒有多余空格
    TRIM(first_name) || ' ' || TRIM(last_name) AS full_name,
    -- 清理部門名稱
    TRIM(department_name) AS clean_department
FROM employee_view
ORDER BY TRIM(last_name), TRIM(first_name); -- 排序時(shí)也使用TRIM確保準(zhǔn)確性