SQL 8 - Data Manipulation, and Conversion

Data Skills in SQL. *Pay Attention*

This post is all about data skills in SQL. Feel free to grab any dataset/SQL of your preference to try these out.

Table of Contents

1. Working With String Data

2. Working With Numeric Data

3. Working With Time/Date Data

4. Converting Data

5. Exercises/Interview Questions

I highly recommend going over the different types of data available in SQL, since this post is all about wrangling data.

You can also watch this video that gives you a quick gloss over the basic data types.

1 - Working With Strings Data

When we are working with string data, sometimes we are interested in manipulating them to something else. SQL gives us 2 types of function for messing around with strings:

• String to Numeric value

• String to String value

Remember, all of these run with the SELECT function, which means none of them will make any permanent changes to your database. Just meant for wrangling data.

Note: Unless you use ALTER TABLE… SET, this will not permanently alter your database.

1.1 String To Numeric Fns

To save you a bunch of time, I made a table that you can look at, to see what is available to you.

Note: STRCMP() is only available for MySQL, while the other 2 are available to everyone

If you want to run these functions on a column, just swap out the strings in the example with the column names.
Example Below:

SELCT Length(my_col) as Len_of_my_col
FROM my_table

1.2 String To String Fns

Enjoy the time saving table below.

String to String SQL data wrangling functions

2 - Working With Numeric Data

There are plenty of functions that deal with handling numeric data. I’ve kicked out the useless ones that you’ll never use, or be quizzed about. AKA the only stuff that’s left is the useful bits.

2.1 Arithmetic Calculations