Dzielenie danych z jednej kolumny na wiele kolumn

Czyszczenie danych w bazach danych SQL Server

Miriam Antona

Software Engineer

Dzielenie produktów i jednostek

paper_shop_monthly_sales

| product_name_units | year_of_sale |
|--------------------|--------------|
| notebooks-150      | 2018         |
| notebooks-200      | 2019         |
| notebooks-30       | 2019         |
| pencils-100        | 2018         |
| pencils-50         | 2018         |
| pencils-130        | 2019         |
| crayons-80         | 2018         |
| ...                | ...          |

Użycie SUBSTRING i CHARINDEX

| product_name_units |
|--------------------|
| notebooks-150      |

| product_name | units |
|--------------|-------|
| notebooks    | 150   |

SUBSTRING(string, start, length)

CHARINDEX(substring, string [,start])

Użycie SUBSTRING i CHARINDEX

SELECT SUBSTRING ('notebooks-150', 1, CHARINDEX('-', 'notebooks-150') - 1) AS product_name

Użycie SUBSTRING i CHARINDEX

SELECT SUBSTRING ('notebooks-150', 1, 9) AS product_name

| product_name |
|--------------|
| notebooks    |

Użycie SUBSTRING i CHARINDEX

SELECT CAST(
    SUBSTRING('notebooks-150', CHARINDEX('-', 'notebooks-150') + 1, LEN('notebooks-150')) 
    AS INT) units

Użycie SUBSTRING i CHARINDEX

SELECT CAST(
    SUBSTRING('notebooks-150', 11, LEN('notebooks-150')) 
    AS INT) units

Użycie SUBSTRING i CHARINDEX

SELECT CAST(
    SUBSTRING('notebooks-150', 11, 13) 
    AS INT) units

| units |
|-------|
| 150   |

Użycie SUBSTRING i CHARINDEX

SELECT 
    SUBSTRING('notebooks-150', 1, CHARINDEX('-', 'notebooks-150') - 1) product_name, 
    CAST
        (SUBSTRING('notebooks-150', CHARINDEX('-', 'notebooks-150') + 1, LEN('notebooks-150')) 
        AS INT) units

| product_name | units |
|--------------|-------|
| notebooks    | 150   |

Użycie LEFT, RIGHT i REVERSE

LEFT(string, number_of_chars)

Pobiera określoną liczbę znaków z lewej strony ciągu

RIGHT(string, number_of_chars)

Pobiera określoną liczbę znaków z prawej strony ciągu

REVERSE(string_expression)

Odwraca ciąg znaków

Użycie LEFT, RIGHT i REVERSE

SELECT
    LEFT('notebooks-150', CHARINDEX('-', 'notebooks-150') - 1) AS product_name,
    RIGHT('notebooks-150', CHARINDEX('-', REVERSE('notebooks-150')) - 1) AS units

Użycie LEFT, RIGHT i REVERSE

SELECT
    LEFT('notebooks-150', 9) AS product_name,
    RIGHT('notebooks-150', CHARINDEX('-', REVERSE('notebooks-150')) - 1) AS units

Użycie LEFT, RIGHT i REVERSE

SELECT
    LEFT('notebooks-150', 9) AS product_name,
    RIGHT('notebooks-150', 4 - 1) AS units

Użycie LEFT, RIGHT i REVERSE

SELECT
    LEFT('notebooks-150', 9) AS product_name,
    RIGHT('notebooks-150', 3) AS units

| product_name | units |
|--------------|-------|
| notebooks    | 150   |

Ćwiczmy!

Czyszczenie danych w bazach danych SQL Server