清理混乱字符串

在 SQL Server 数据库中清洗数据

Miriam Antona

Software Engineer

去除多余空格

SELECT * FROM carriers

| code | name                            |
|------|---------------------------------|
| YV   | Mesa Airlines Inc.              |
| AA   |   American Airlines Inc.        |
| B6   |   JetBlue Airways               |
| DL   |   Delta Air Lines Inc.          |
| HA   |   Hawaiian Airlines Inc.        |
| MQ   |   American Eagle Airlines Inc.  |
| EV   |  ExpressJet Airlines Inc.       |
| UA   |  United Air Lines Inc.          |
| US   |  US Airways Inc.                |
| ...  | ...                             |
在 SQL Server 数据库中清洗数据

去除多余空格 - TRIM

TRIM ( [characters ] string )
  • 自 SQL Server 2017 起可用
  • 从字符串首尾移除指定字符
  • 如未指定字符，则移除空格
SELECT TRIM('   JetBlue Airways    ');

JetBlue Airways
在 SQL Server 数据库中清洗数据

去除多余空格 - RTRIM 与 LTRIM

  • 早于 SQL Server 2017 的版本使用 RTRIM 和 LTRIM。
-- 移除结尾全部空格
RTRIM ( character_expression )

-- 移除开头全部空格
LTRIM ( character_expression )

SELECT LTRIM(RTRIM('   JetBlue Airways    '));

JetBlue Airways
在 SQL Server 数据库中清洗数据

去除多余空格

SELECT code, TRIM(name) AS name FROM carriers

SELECT code, LTRIM(RTRIM(name)) AS name FROM carriers

| code | name                         |
|------|------------------------------|
| YV   | Mesa Airlines Inc.           |
| AA   | American Airlines Inc.       |
| B6   | JetBlue Airways              |
| DL   | Delta Air Lines Inc.         |
| HA   | Hawaiian Airlines Inc.       |
| US   | US Airways Inc.              |
| ...  | ...                          |
在 SQL Server 数据库中清洗数据

统一字符串

SELECT * FROM airports 
ORDER BY airport_state

| airport_code | airport_name                            | airport_city    | airport_state |
|--------------|-----------------------------------------|-----------------|---------------|
| ...          | ...                                     | ...             | ...           |
| MIA          | Miami International                     | Miami           | fl            |
| TPA          | Tampa International                     | Tampa           | Fl            |
| FLL          | Fort Lauderdale-Hollywood International | Fort Lauderdale | FL            |
| MCO          | Orlando International                   | Orlando         | Florida       |
| ...          | ...                                     | ...             | ...           |
在 SQL Server 数据库中清洗数据

统一字符串 - REPLACE

"Fl" / "fl" / "Florida" -> "Florida"

REPLACE ( string_to_replace , occurrences , string_replacement )

  • 将指定字符串的所有出现替换为另一字符串

  • 按输入的排序规则进行比较

    • 使用 COLLATE 以特定排序规则比较
    • 假设输入的排序规则不区分大小写
在 SQL Server 数据库中清洗数据

统一字符串 - REPLACE

SELECT 
    airport_code, airport_name, airport_city, 
    REPLACE(airport_state, 'FL', 'Florida') AS airport_state
FROM airports
ORDER BY airport_state

| airport_code | airport_name                            | airport_city    | airport_state |
|--------------|-----------------------------------------|-----------------|---------------|
| ...          | ...                                     | ...             | ...           |
| MIA          | Miami International                     | Miami           | Florida       |
| TPA          | Tampa International                     | Tampa           | Florida       |
| FLL          | Fort Lauderdale-Hollywood International | Fort Lauderdale | Florida       |
| MCO          | Orlando International                   | Orlando         | Floridaorida  |
| ...          | ...                                     | ...             | ...           |
在 SQL Server 数据库中清洗数据

统一字符串 - REPLACE

SELECT 
    airport_code, airport_name, airport_city, 
    REPLACE
        (REPLACE(airport_state, 'FL', 'Florida'),
         'Floridaorida', 'Florida') AS airport_state
FROM airports
ORDER BY airport_state

| airport_code | airport_name                            | airport_city    | airport_state |
|--------------|-----------------------------------------|-----------------|---------------|
| MCO          | Orlando International                   | Orlando         | Florida       |
| TPA          | Tampa International                     | Tampa           | Florida       |
| FLL          | Fort Lauderdale-Hollywood International | Fort Lauderdale | Florida       |
| MIA          | Miami International                     | Miami           | Florida       |
| ...          | ...                                     | ...             | ...           |
在 SQL Server 数据库中清洗数据

统一字符串 - REPLACE + CASE

SELECT airport_code, airport_name, airport_city, 
    CASE
        WHEN airport_state <> 'Florida' THEN REPLACE(airport_state, 'FL', 'Florida')
        ELSE airport_state 
    END AS airport_state
FROM airports
ORDER BY airport_state

| airport_code | airport_name                            | airport_city    | airport_state |
|--------------|-----------------------------------------|-----------------|---------------|
| MCO          | Orlando International                   | Orlando         | Florida       |
| TPA          | Tampa International                     | Tampa           | Florida       |
| FLL          | Fort Lauderdale-Hollywood International | Fort Lauderdale | Florida       |
| MIA          | Miami International                     | Miami           | Florida       |
| ...          | ...                                     | ...             | ...           |
在 SQL Server 数据库中清洗数据

统一字符串 - REPLACE + UPPER

"Fl" / "fl" / "Florida" -> "FL"

SELECT 
    airport_code, airport_name, airport_city, 
    REPLACE(airport_state, 'Florida', 'FL') AS airport_state
FROM airports
ORDER BY airport_state

| airport_code | airport_name                            | airport_city    | airport_state |
|--------------|-----------------------------------------|-----------------|---------------|
| MCO          | Orlando International                   | Orlando         | FL            |
| TPA          | Tampa International                     | Tampa           | Fl            |
| FLL          | Fort Lauderdale-Hollywood International | Fort Lauderdale | FL            |
| MIA          | Miami International                     | Miami           | fl            |
| ...          | ...                                     | ...             | ...           |
在 SQL Server 数据库中清洗数据

统一字符串 - REPLACE + UPPER

UPPER ( character_expression )
  • 将字符串转换为大写。
SELECT 
    airport_code, airport_name, airport_city, 
        UPPER(
              REPLACE(airport_state, 'Florida', 'FL')
        ) AS airport_state
FROM airports
ORDER BY airport_state
在 SQL Server 数据库中清洗数据

统一字符串 - REPLACE + UPPER

| airport_code | airport_name                            | airport_city    | airport_state |
|--------------|-----------------------------------------|-----------------|---------------|
| ...          | ...                                     | ...             | ...           |
| MCO          | Orlando International                   | Orlando         | FL            |
| TPA          | Tampa International                     | Tampa           | FL            |
| FLL          | Fort Lauderdale-Hollywood International | Fort Lauderdale | FL            |
| MIA          | Miami International                     | Miami           | FL            |
| ...          | ...                                     | ...             | ...           |
在 SQL Server 数据库中清洗数据

Vamos praticar!

在 SQL Server 数据库中清洗数据

Preparing Video For Download...