Converting data with different types

Cleaning Data in SQL Server Databases

Miriam Antona

Software Engineer

Introduction to undesirable data types

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 to undesirable data types

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 to undesirable data types

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

Undesirable data types - examples

SELECT * FROM series
WHERE rating BETWEEN 0 AND 10

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

Conversion failed when converting the varchar value '8.4' to data type int.

Undesirable data types - examples

SELECT AVG(rating)
FROM series

Operand data type varchar is invalid for avg operator.

Solving type conversion problems

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    | ... |
| ... | ...             | ...        | ...            | ...    | ... |

Solving type conversion problems

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 |

Let's practice!

Cleaning Data in SQL Server Databases