Convertir des données de types différents

Nettoyer des données dans des bases de données SQL Server

Miriam Antona

Software Engineer

Introduction aux types de données indésirables

SELECT * FROM series
WHERE rating BETWEEN 0 AND 10

SELECT * FROM series
WHERE rating >= 0 AND rating <= 10

| id  | name            | rating | ... |
|-----|-----------------|--------|-----|
| 1   | Adventure Time  | 8.4    | ... |
| 2   | Dexter          | 8.6    | ... |
| 3   | Futurama        | 9.2    | ... |
| 4   | Game of Thrones | 9.3    | ... |
| ... | ...             | ...    | ... |

Introduction aux types de données indésirables

SELECT * FROM series
WHERE rating BETWEEN 0 AND 10

SELECT * FROM series
WHERE rating >= 0 AND rating <= 10

| id  | name            | rating | ... |
|-----|-----------------|--------|-----|
| 1   | Adventure Time  | 8.4    | ... |
| 2   | Dexter          | 8.6    | ... |
| 3   | Futurama        | 9.2    | ... |
| 4   | Game of Thrones | 9.3    | ... |
| ... | ...             | ...    | ... |

Column_name	Type
id	int
name	varchar
premiered	date
official_site	varchar
contact_number	varchar
rating	float
summary	varchar

Introduction aux types de données indésirables

Column_name	Type
id	int
name	varchar
premiered	date
official_site	varchar
contact_number	varchar
rating	varchar
summary	varchar

Types de données indésirables – exemples

SELECT * FROM series
WHERE rating BETWEEN 0 AND 10

SELECT * FROM series
WHERE rating >= 0 AND rating <= 10

Échec de la conversion lors du passage de la valeur varchar '8.4' au type de données int.

Types de données indésirables – exemples

SELECT AVG(rating)
FROM series

Le type de données varchar est invalide pour l'opérateur avg.

Résoudre les problèmes de conversion de type

SELECT * FROM series
WHERE CAST(rating AS FLOAT) BETWEEN 0 AND 10

SELECT * FROM series
WHERE CONVERT(FLOAT, rating) BETWEEN 0 AND 10

| id  | name            | premiered  | contact_number | rating | ... |
|-----|-----------------|------------|----------------|--------|-----|
| 1   | Adventure Time  | 2010-04-05 | 555-906-8845   | 8.4    | ... |
| 2   | Dexter          | 2006-01-10 | 555-156-8845   | 8.6    | ... |
| 3   | Futurama        | 1999-03-28 | 555-210-9951   | 9.2    | ... |
| 4   | Game of Thrones | 2011-04-17 | 555-543-6641   | 9.3    | ... |
| ... | ...             | ...        | ...            | ...    | ... |

Résoudre les problèmes de conversion de type

SELECT AVG(CAST(rating AS FLOAT)) AS rating_casted
FROM series
WHERE CAST(rating AS FLOAT) BETWEEN 0 AND 10

SELECT AVG(CONVERT(FLOAT, rating)) AS rating_casted
FROM series
WHERE CONVERT(FLOAT, rating) BETWEEN 0 AND 10

| rating_casted    |
|------------------|
| 8,62857142857143 |

Passons à la pratique !

Nettoyer des données dans des bases de données SQL Server