Video summary
This video tutorial introduces essential string and date functions within PostgreSQL designed to streamline data manipulation and analysis. The instructor begins by demonstrating how to standardize text data using the `upper` and `lower` functions, which convert character names to all capital or lowercase letters respectively, a common practice for ensuring consistency across different database systems. Following this, the lesson covers the `length` function, which counts the number of characters in a string; the presenter shares a practical tip on casting numeric columns to text using the double colon operator so that length calculations can be performed on monetary values or phone numbers to validate data integrity or identify anomalies based on character count.
The tutorial then explores functions for extracting specific portions of text, starting with `left` and `right`, which retrieve a defined number of characters from the beginning or end of a string. The instructor highlights `substring` as a particularly versatile tool that allows users to extract any specific range of characters by defining both a starting position and an ending position, offering more flexibility than the directional functions. Additionally, the video explains how to combine multiple text fields into a single coherent sentence using the `concat` function, which is useful for merging separate columns like first names, last names, cities, and states into one readable address or profile line. Finally, the `replace` function is demonstrated as a method to correct data errors or conditionally update values, such as changing "no" to "yes" for individuals meeting certain wealth criteria, showing how string functions can be combined with conditional logic in queries.
Transitioning to date operations, the video explains how to work with temporal data using built-in commands like `current_date` to retrieve today's date and subtract it from a birth date to calculate age in days or years. The instructor also introduces the `extract` function, which isolates specific components of a date such as the year, month, or day for detailed reporting. To handle time-based calculations, the lesson covers intervals, allowing users to add or subtract specific durations like ten years or ten days from a given date to determine future events like birthdays. The session concludes with the `date_trunc` function, described as a rounding tool that truncates dates down to a specific precision, such as the first day of a year or the first day of a month, which is invaluable for grouping data by time periods without altering the underlying records.
Overall, mastering these functions significantly enhances a user's ability to clean, validate, and analyze data efficiently in PostgreSQL. The presenter emphasizes that while some functions might seem simple at first glance, their real-world applications are vast, ranging from validating phone number formats to calculating precise ages and organizing large datasets by time intervals. By utilizing these pre-built commands, developers and analysts can avoid writing complex custom logic for common tasks, thereby making their workflow much easier and more robust. The video serves as a comprehensive guide for anyone looking to expand their SQL skillset with practical tools that are used daily in professional database environments.
Read the full video transcript
What's going on everybody? Welcome back
to another video. Today we are
continuing our Postgrace SQL series and
in this lesson we're going to be looking
at string and date functions. Now all
functions are are pre-builtin commands
within Postgra SQL to do specific
things. And a lot of these functions are
just really useful. It makes your life
so much easier. And so knowing how to
use them and what they do, that is half
the battle. And so I'm going to show you
some of my favorite string and date
functions, ones that I've just been
using for years. I use them all the time
and they're worth knowing. Let's not
waste any time. Let's get right into it.
Let's start with this character name
right here. So we're going to do
character
name. Now with this character name,
sometimes they're formatted perfectly,
and they are like this. They're just
formatted great. They look wonderful,
but sometimes they're not. And a lot of
times when I'm cleaning data or I want
to standardize data, I'll use an upper
like this. This is an upper string
function and I just pass through the
character name. And if we run this,
we'll get the exact same column, but now
they are all in uppercase. Now, of
course, things like numbers aren't going
to change, but if it is a text
character, which is a through z, those
are all going to be capitalized. You can
do the same thing by doing a comma here.
And we're going to say lower. And then
we're going to pass through our
character name as well into this
function. Whoops. There we go. And let's
go ahead and run this. And now these are
all lowercase. And so these are two
functions where you actually changing
whether something is uppercase or
lowercase. And oftentimes you'll see
some in all uppercase just by default in
certain systems and databases because
again they are trying to standardize
things instead of having one be Luke
Skywalker like this and another be Luke
Skywalker all caps. They just want them
all to be all caps and so they do that
by default. The next string function
that I want to show you is length. And
length is a really good one because it's
going to actually count how many
characters are in each cell. So let's go
ahead and run this. Take a look at what
it gives us. So we have Luke Skywalker
that has a length of 14. Leia Orana that
has a length of 11. And so it just
counts how many characters you have and
it gives you the output. And you may be
thinking that doesn't sound very
exciting, but there's actually a lot of
kind of use cases for this. Let's just
look at our table and I'll give you a
very simple example. For example, we
have estimated net worth. So, let's take
this column, if I can spell it right.
There we go. And I'm going to do the
length. I'm going to cop Oops. I'm going
to copy this and do the length of
estimated net worth. Let's go ahead and
run this. That's not going to work
because of course this is a numeric
column and that's to be expected. But
I'm going to give you a little secret
here. This is a little secret sauce.
Something that I do all the time. You
can just convert it. you just make it
into a string. All you have to do is
come right here, do a double colon and
say text. And let's go ahead and run
this. So when we were getting our
length, all we did was convert this into
a text so that we can use the string
function. This is a function that works
on text and strings, not numeric and not
dates. So there are separate functions
for specific data types. But this is
just a little workaround. So now this
would be a use case where I would say if
their net worth is 9 10 9 11 10 87 I can
kind of see how much money they have,
what their income bracket is just by
looking at the length of their actual
estimated net worth. This is another
example I've used it for in the real
world where I was looking at phone
numbers and phone numbers only supposed
to have a specific amount of characters
and so I would convert it into
characters and I would check it and if
there was one that had like 20
characters I knew that that was one I
needed to check on. And so I could
filter on that and I could get rid of
it. So this is something that I've used
for a lot of different scenarios. It
just kind of depends on what you're
using it for. Let's come down here and
let's look at the next one. And let's do
select everything. Need to spell select,
right? So when you're looking especially
at strings, sometimes you just want to
take a portion of those strings. You
don't want to take all of them, right?
You just want to take some of the string
but not all of it. For example, let's
come up here and let's take a look at
the left. I'm going to do an open
parenthesis. And what we're going to do
is we're going to pass through the
character name. So, I'm going to come
back up just to copy it, make it go a
little faster. But I'm going to take the
character name and then within this
left, we're going to start on this left
hand side. And we can specify how many
values we want to go over. So, I'm going
to say character name. Then I'm going to
do a comma. I'm going to say four. Let's
just say we want the first four letters
of their name. Let's go ahead and run
this. So now for Luke, we have Luke.
Leia, we have Leia. For Han Solo, we
have Han. For Chewbacca, we just have
Chu. We're taking the first four
characters from the left hand side. We
can do the exact opposite of this. If we
copy this over, we can change this to,
you guessed it, the right. Now we're
going to start from the right hand side
and go over four. Let's go ahead and run
this. Now we're starting from the right
hand side. We're going over four and we
are getting our output. The next thing
we're going to look at is my personal
favorite. This is probably the one I use
more than anything. This is substring.
And substring is great because you don't
have to just start on the left. You
don't just have to start on the right.
You can start anywhere you want. So
let's specify that we want it from the
character name. And now we have to pass
through two things. We have to pass
through the starting position and the
ending position. So we'll say from to
and we'll say four four. And there isn't
a comma here. That's my bad. Now let's
just go ahead and run this. And now we
have uke. So we're starting at position
two, which is the u. And then we're
going to position four. U k and e. So
we're selecting three characters from
this string. So this one can be really
good. It just depends on the kind of
data that you're working with. But if
you need to select specific strings or
characters from your text, this can be
really, really useful. Now, let's look
at the next one. Let's come right down
here. Go to select everything.
Now, the next one is concat. And concat
stands for concatenate. It just means to
combine strings. That's all. And so, we
have two strings here. And let's do it
like this. So, we're going to say concat
and then within our parenthesis, we're
going to take the character name and I
don't want to rewrite this. Let's take
our character name. Then we'll do a
comma. And now we can pass through
another string. And we'll make this one
like this is a and then we'll do a comma
species.
So, we're going to say the character
name Luke Skywalker is a and then the
species human. Let's go ahead and run
this. And it looks like I need a space
after this really quick. Let's run that.
There we go.
And now we have Luke Skywalker is a
human. And we can add more to this.
Let's say we want to add a period. Oops.
I'm going to do single quotes.
And let's run that. Let's put a comma
there. Let's go ahead and run this. Now
we have Luke Skywalker is a human.
Largon is a human. R2-D2 is a droid. Now
we've made a sentence from this. We've
combined characters. Now a lot of times
this is actually used with things like
city, state, address, first names, last
names where you want to combine those
things into one single column instead of
having your data separated into multiple
columns where it's not as useful. So
that is concat aka concatenation. Now
let's move on to the next one. And this
next one is really good. It's replace.
Now why would you want to replace
something? Maybe a value is incorrect.
Maybe you just don't like how it looks.
It doesn't matter. You can replace it.
For example, we have has both arms is
equal to n. And so what we can do is we
can say replace, let me spell this
right, replace. And we'll say has
both arms. And we're going to replace
the no with a yes. So let's go ahead and
run this. And I have too many
parentheses here. Let's get rid of that.
And let's run it. And now in our output,
you can see for the has both arms, we've
put it as a yes for everything. And we
could do everything, here, let's go
ahead and run this. So now before we had
no, yes, yes, yes, no. And here we have
all just yeses. Now this might be an
example of if we're replacing a
character, we can say, okay, we want to
give him his arm back if he's rich
because maybe he bought another one. So
let's look at Darth Vader. He has like
10 million. Let's put it over a million.
We're going to say where estimated net
worth is greater than and I'll say I
think that's a million right there.
Let's go ahead and run this. I think
it's too much. Let's go ahead and run
this. There we go. So now we've replaced
the text only for people who have an
estimated net worth of greater than that
amount. We're going to make sure that
they have both arms. Of course, we can
call this and we can say uh as and we
can name this now has both
underscore arms
and we'll run that and there we go. So
replace is going to take in that column
and you need to specify what you're
looking for and then what you're
replacing it with. Of course you can add
conditions to this as well in your
query. Now let's come down because we're
now done with the string functions. Now
we are going on to the date functions.
Now the only date column that we have is
birth date. So I'm going to put birth
date right here. And let's run this. The
first one that we're going to look at
actually doesn't have to do with this
column, but I'm going to, you know, keep
it there anyways, but it's called
current date. So I'm going to come right
here. I'm going to say current
date. And let's go ahead and run this.
And so this is today's current date.
This is when I'm recording it, the
January 22nd of 2026. This is compared
to their birth date. And so you can see
we can now start using this date to then
maybe subtract it from their birth date.
Let's actually do that in this column
right over here. So we're going to say
current date
and we'll do minus their birth date and
we'll say that's as days alive. That
should give us a date in terms of actual
days, not months or years. So, this
person has been alive 17,651
days. Of course, we can also add their
character name so we can see who this
actually is. Let's go and run that. But
that current date is really useful. Now,
there are some built-in functions to
kind of do something like this because
maybe we're trying to calculate how long
they've been alive. We could also, and
I'm going to come right here, we could
also just say their age. So, if we take
their age and we plug it into the birth
date, let's go ahead and run this. It's
going to give us how long that person
has been alive. So, Luke Skywalker was
born in 1977. He's been alive 48 years,
3 months, and 27 days. So, these are
both really good kind of builtin
functions that you can use with the
dates. Another really useful thing, and
let's come right here, is we can do
extract. Now, extract pulls different
parts out of the date. So, let's say we
want to extract. We're going to pass
through birth date after we specify the
measurement that we're looking for. So,
we're going to say year from birth date.
So, we're just extracting the year now.
So, now we have the year pulled out.
There's 1977.
There we go. We're going to do the exact
same thing. And you can guess it.
We're going to Whoops. say a comma
there. We're going to do this for month
and let me put that all caps. Month and
day. Now we can run this.
And here's our month we're extracting
and here's the day that we're
extracting. So we have 925 9 and 25. So
we're just extracting information out of
the already existing date column. Now
this is starting to get a bit much.
Let's come down here and I'm going to
just keep this information actually. So,
let's go ahead and run this. The next
one that I want to show you is
intervals. Intervals are really
important. Essentially, what they do is
they let you specify how much you want
to add or subtract from a specific day.
So, what we can do is we can take birth
date. And let me actually just bring
this down to another line. So, we're
going to say birthday and then we're
going to say plus and I'm going to say
when did this person turn 10 years old
or when will this person turn 10 years
old. So, we can say interval and then
we're going to specify 10 years. So,
we're doing an interval of 10 years from
this birth date. Let's go ahead and run
this. So, now we have 1977
925 1987 925. These intervals can be
really customizable. So let's come down
here and we'll do another one. We can do
an interval of 10 days for example.
Let's go ahead and run this. So now this
is 10 days later. You can see 925 goes
into the next month of 10:05. Now the
very last one that we are going to take
a look at and we can keep it in this one
why not is truncate. Now truncate is
kind of like a rounding function for a
date column. If we do date
trunk and I need to spell this right. If
we do date truncate, we can pass through
the measurement that we're wanting. So
this will be right at the beginning.
We're going to say year and then we'll
do a comma and we'll have the birth
date. Let's go ahead and run this and
see what it looks like. So for this,
this person Luke Skywalker was born in
1977. So was Leia. They were born on the
same day. Shocking. But if we come over
here to the date truncated is going to
the very first day of that year. So
essentially rounding down to the year
that they were born or the very first
day on the year that they were born. We
can do the exact same thing. Let me get
that common there. We can do the very
same thing, but we could specify we want
the month. And let's go ahead and run
this. So now it's rounding down to the
month. So their birth date was 9:25.
Now it's going to 9001. So those are a
lot of the really popular string and
date functions within Postgrace SQL. If
you already knew all of these, you're a
rock star. I don't know why you're
watching this. But if you didn't know
all of these, I'm glad that you're
learning them because they're so so
useful. If you learned anything, be sure
to like and subscribe. And I will see
you in the next lesson.
[music]