Converting and Formatting Data

Convert and Format Data

Using the Spreadsheet

Using SQL

The CAST function:

CAST (expression AS typename)
#expression is the data to be converted 
#typename is the data type to be returned

Converting a number to a string

SELECT CAST (MyCount AS STRING) FROM MyTable
#SELECT indicates that you will be selecting data from a table
#CAST indicates that you will be converting the data you select to a different data type
#AS comes before and identifies the data type which you are casting to
#STRING indicates that you are converting the data to a string
#FROM indicates which table you are selecting the data from

Converting a string to a number

SELECT CAST (MyVarcharCol AS INT) FROM MyTable
#SELECT indicates that you will be selecting data from a table
#CAST indicates that you will be converting the data you select to a different data type
#AS comes before and identifies the data type which you are casting to
#INT indicates that you are converting the data to an integer
#FROM indicates which table you are selecting the data from

Converting a date to a string

SELECT CAST (MyDate AS STRING) FROM MyTable
#SELECT indicates that you will be selecting data from a table
#CAST indicates that you will be converting the data you select to a different data type
#AS comes before and identifies the data type which you are casting to
#STRING indicates that you are converting the data to a string
#FROM indicates which table you are selecting the data from

Converting a date to a datetime

SELECT CAST (MyDate AS DATETIME) FROM MyTable
#SELECT indicates that you will be selecting data from a table
#CAST indicates that you will be converting the data you select to a different data type
#AS comes before and identifies the data type which you are casting to
#DATETIME indicates that you are converting the data to a datetime value
#FROM indicates which table you are selecting the data from

The SAFE_CAST function:

SELECT SAFE_CAST (MyDate AS STRING) FROM MyTable

Combine Multiple Datasets

Dataset: A collection of data that can be manipulated or analyzed as one unit.

Ranking: A system to position values of a dataset within a scale of achievement or status.

Using the CONCAT function

SELECT CONCAT ('Data','analysis')

The result will be Dataanalysis.

SELECT CONCAT ('Data',' ','analysis')

The result will be Data analysis.

CONCAT_WS: A function that adds two or more strings together with a separator.

CONCAT_WS (‘ . ’, ‘www’, ‘google’, ‘com’)
#The separator (being the period) gets input before and after Google when you run the SQL function

CONCAT with +: Adds two or more strings together using the + operator

LIMIT: A SQL clause that specifies the maximum number of records returned in a query.

Example:

SELECT
    usertype,
    CONCAT(start_station_name, " to ", end_station_name) AS route,
    COUNT(*) as num_trips,
    ROUND(AVG(CAST(tripduration as int64)/60),2) AS duration
FROM
    'bigquery-public-data,new_york,citibike_trips'
GROUP BY
    start_station_name, end_station_name, usertype
ORDER BY
    num_trips DESC
LIMIT 10

ROUND: A SQL function that returns a number rounded to a certain number of decimal places.