- 2 days ago
String and date functions in PostgreSQL can make everyday querying, cleaning, and standardizing data much easier. This lesson walks through practical examples of upper, lower, length, left, right, substring, concat, and replace, showing how each one changes text and helps you work with messy or inconsistent values.
It also covers useful date functions like current date, age, extract, intervals, and date_trunc. You’ll see how to calculate days alive, pull out year, month, and day from a birth date, add time intervals, and round dates down to the start of a year or month. These PostgreSQL tips are especially helpful for SQL beginners, data cleaning, and anyone learning how to go beyond basic querying.
Created for learners searching for PostgreSQL string functions, PostgreSQL date functions, SQL text functions, date_trunc, extract from date, interval in PostgreSQL, and practical SQL tutorials. Great for database practice, data analysis workflows, and building stronger PostgreSQL skills with real examples.
It also covers useful date functions like current date, age, extract, intervals, and date_trunc. You’ll see how to calculate days alive, pull out year, month, and day from a birth date, add time intervals, and round dates down to the start of a year or month. These PostgreSQL tips are especially helpful for SQL beginners, data cleaning, and anyone learning how to go beyond basic querying.
Created for learners searching for PostgreSQL string functions, PostgreSQL date functions, SQL text functions, date_trunc, extract from date, interval in PostgreSQL, and practical SQL tutorials. Great for database practice, data analysis workflows, and building stronger PostgreSQL skills with real examples.
Category
📚
LearningTranscript
00:00What's going on, everybody? Welcome back to another video. Today, we are continuing our PostgreSQL series, and in this lesson,
00:06we're going to be looking at string and date functions.
00:09Now, all functions are pre-built-in commands within PostgreSQL to do specific things, and a lot of these functions
00:18are just really useful.
00:20It makes your life so much easier, and so knowing how to use them and what they do, that is
00:25half the battle.
00:26And so I'm going to show you some of my favorite string and date functions, the ones that I've just
00:29been using for years.
00:30I use them all the time, and they're worth knowing.
00:33Let's not waste any time. Let's get right into it.
00:36Let's start with this character name right here.
00:38So we're going to do character underscore name.
00:42Now, with this character name, sometimes they're formatted perfectly, and they are like this.
00:48They're just formatted great. They look wonderful, but sometimes they're not.
00:53And a lot of times when I'm cleaning data or I want to standardize data, I'll use an upper like
00:59this.
01:00This is an upper string function, and I just pass through the character name.
01:05And if we run this, we'll get the exact same column, but now they are all in uppercase.
01:11Now, of course, things like numbers aren't going to change, but if it is a text character, which is A
01:18through Z, those are all going to be capitalized.
01:21You can do the same thing by doing a comma here, and we're going to say lower, and then we're
01:26going to pass through our character name as well into this function.
01:29Whoops. There we go.
01:30And let's go ahead and run this.
01:32And now these are all lowercase.
01:35And so these are two functions where you are actually changing whether something is uppercase or lowercase.
01:40And oftentimes you'll see some in all uppercase just by default in certain systems and databases because, again, they are
01:47trying to standardize things.
01:49Instead of having one be Luke Skywalker like this and another be Luke Skywalker all caps, they just want them
01:55all to be all caps.
01:56And so they do that by default.
01:57The next string function that I want to show you is length.
02:00And length is a really good one because it's going to actually count how many characters are in each cell.
02:07So let's go ahead and run this, take a look at what it gives us.
02:11So we have Luke Skywalker.
02:13That has a length of 14.
02:15Leia Organa.
02:16That has a length of 11.
02:18And so it just counts how many characters you have, and it gives you the output.
02:21And you may be thinking that doesn't sound very exciting, but there's actually a lot of kind of use cases
02:28for this.
02:28Let's just look at our table, and I'll give you a very simple example.
02:33For example, we have estimated net worth.
02:35So let's take this column, if I can spell it right.
02:40There we go.
02:41And I'm going to do the length.
02:43I'm going to copy this and do the length of estimated net worth.
02:50Let's go ahead and run this.
02:51That's not going to work because, of course, this is a numeric column, and that's to be expected.
02:56But I'm going to give you a little secret here.
02:58This is a little secret sauce, something that I do all the time.
03:01You can just convert it.
03:02You just make it into a string.
03:05All you have to do is come right here, do a double colon, and say text.
03:10And let's go ahead and run this.
03:11So when we were getting our length, all we did was convert this into a text so that we can
03:18use the string function.
03:19This is a function that works on text and strings, not numeric, not dates.
03:24So there are separate functions for specific data types.
03:28But this is just a little workaround.
03:30So now this would be a use case where I would say if their net worth is 9, 10, 9,
03:3511, 10, 8, 7, I can kind of see how much money they have, what their income bracket is, just
03:41by looking at the length of their actual estimated net worth.
03:45This is another example I've used it for in the real world where I was looking at phone numbers, and
03:49phone numbers are only supposed to have a specific amount of characters.
03:52And so I would convert it into characters, and I would check it.
03:55And if there was one that had like 20 characters, I knew that that was one I needed to check
03:59on.
04:00And so I could filter on that, and I could get rid of it.
04:02So this is something that I've used for a lot of different scenarios.
04:04It just kind of depends on what you're using it for.
04:07Now let's come down here, and let's look at the next one.
04:11And let's do select everything.
04:13You just spell select, right?
04:15So when you're looking, especially at strings, sometimes you just want to take a portion of those strings.
04:22You don't want to take all of them, right?
04:24You just want to take some of the string, but not all of it.
04:27For example, let's come up here, and let's take a look at the left.
04:32I'm going to do an open parenthesis.
04:34And what we're going to do is we're going to pass through the character name.
04:37So I'm going to come back up just to copy it, make it go a little faster.
04:41But I'm going to take the character name, and then within this left, we're going to start on this left
04:46-hand side,
04:47and we can specify how many values we want to go over.
04:50So I'm going to say character name, and then I'm going to do a comma.
04:54I'm going to say four.
04:55Let's just say we want the first four letters of their name.
05:00Let's go ahead and run this.
05:02So now for Luke, we have Luke.
05:04Leia, we have Leia.
05:05For Han Solo, we have Han.
05:07For Chewbacca, we just have Chew.
05:08We're taking the first four characters from the left-hand side.
05:12We can do the exact opposite of this.
05:15If we copy this over, we can change this to, you guessed it, the right.
05:21Now we're going to start from the right-hand side and go over four.
05:25Let's go ahead and run this.
05:27Now we're starting from the right-hand side.
05:29We're going over four, and we are getting our output.
05:32The next thing we're going to look at is my personal favorite.
05:35This is probably the one I use more than anything.
05:37This is substring.
05:40And substring is great because you don't have to just start on the left.
05:43You don't just have to start on the right.
05:44You can start anywhere you want.
05:46So let's specify that we want it from the character name.
05:50And now we have to pass through two things.
05:51We have to pass through the starting position and the ending position.
05:55So we'll say from two, and we'll say for four.
05:59And there isn't a comma here.
06:00That's my bad.
06:01Now let's just go ahead and run this.
06:04And now we have Luke.
06:06Luke, so we're starting at position two, which is the U.
06:09And then we're going to position four, U, K, and E.
06:13So we're selecting three characters from this string.
06:17So this one can be really good.
06:19It just depends on the kind of data that you're working with.
06:22But if you need to select specific strings or characters from your text,
06:25this can be really, really useful.
06:26Now let's look at the next one.
06:29Let's come right down here.
06:30Go to select everything.
06:33Now, the next one is concat.
06:36And concat stands for concatenate.
06:39It just means to combine strings.
06:41That's all.
06:42And so we have two strings here.
06:44And let's do it like this.
06:46We're going to say concat.
06:48And then within our parentheses, we're going to take the character name.
06:52And I don't want to rewrite this.
06:53Let's take our character name.
06:54Then we'll do a comma.
06:56And now we can pass through another string.
06:58And we'll make this one like this.
06:59Is a, and then we'll do a comma, species.
07:04So we're going to say the character name, Luke Skywalker, is a, and then the species, human.
07:10Let's go ahead and run this.
07:13And it looks like I need a space after this.
07:17Really quick, let's run that.
07:18There we go.
07:21And now we have Luke Skywalker, is a human.
07:23And we can add more to this.
07:24Let's say we want to add a period.
07:26I'm going to do single quotes.
07:29And let's run that.
07:30Let's put a comma there.
07:32Let's go ahead and run this.
07:34Now we have Luke Skywalker, is a human.
07:36Leora Gunn, is a human.
07:37R2-D2, is a droid.
07:39Now we've made a sentence from this.
07:41We've combined characters.
07:42Now, a lot of times, this is actually used with things like city, state, address, first names, last names.
07:48Where you want to combine those things into one single column, instead of having your data separated into multiple columns,
07:55where it's not as useful.
07:56So that is concat, aka concatenation.
08:00Now, let's move on to the next one.
08:02And this next one is really good.
08:05It's replace.
08:06Now, why would you want to replace something?
08:09Maybe a value is incorrect.
08:11Maybe you just don't like how it looks.
08:13It doesn't matter.
08:14You can replace it.
08:15For example, we have has both arms is equal to n.
08:20And so what we can do is we can say, replace, let me spell this right, replace, and we'll say
08:27has both arms.
08:30And we're going to replace the no with a yes.
08:35So let's go ahead and run this.
08:37And I have too many parentheses here.
08:39Let's get rid of that.
08:41And let's run it.
08:42And now in our output, you can see for the has both arms, we've put it as a yes for
08:46everything.
08:46And we could do everything comma here.
08:50Let's go ahead and run this.
08:51So now before we had no, yes, yes, yes, no.
08:56And here we have all just yeses.
08:58Now, this might be an example of if we're replacing a character, we can say, okay, we want to give
09:04him his arm back if he's rich because maybe he bought another one.
09:07So let's look at Darth Vader.
09:09He has like 10 million.
09:11Let's put it over a million.
09:12We're going to say where estimated net worth is greater than, and I'll say, I think that's a million right
09:21there.
09:21Let's go ahead and run this.
09:23I didn't think of too much.
09:25Let's go ahead and run this.
09:26There we go.
09:27So now we've replaced the text only for people who have an estimated net worth of greater than that amount.
09:33We're going to make sure that they have both arms.
09:36Of course, we can call this, and we can say as, and we can name this now has both underscore
09:45arms.
09:46And I'll run that.
09:49And there we go.
09:50So replace is going to take in that column, and you need to specify what you're looking for and then
09:56what you're replacing it with.
09:58Of course, you can add conditions to this as well in your query.
10:02Now, let's come down because we're now done with the string functions.
10:06Now, we are going on to the date functions.
10:09Now, the only date column that we have is birth date.
10:14So I'm going to put birth underscore date right here, and let's run this.
10:19The first one that we're going to look at actually doesn't have to do with this column, but I'm going
10:22to, you know, keep it there anyways.
10:24But it's called current date.
10:26So I'm going to come right here.
10:27I'm going to say current underscore date.
10:31And let's go ahead and run this.
10:33And so this is today's current date.
10:35This is when I'm recording it, the January 22nd of 2026.
10:39This is compared to their birth date.
10:42And so you can see we can now start using this date to then maybe subtract it from their birth
10:47date.
10:48Let's actually do that in this column right over here.
10:49So we're going to say current date.
10:53And we'll do minus their birth date.
10:56And we'll say that's as days underscore alive.
10:59That should give us a date in terms of actual days, not months or years.
11:04So this person has been alive 17,651 days.
11:09Of course, we can also add their character name so we can see who this actually is.
11:15Go ahead and run that.
11:16But that current date is really useful.
11:19Now, there are some built-in functions to kind of do something like this because maybe we're trying to calculate
11:25how long they've been alive.
11:26We could also, and I'm going to come right here, we could also just say their age.
11:31So if we take their age and we plug it into the birth date, let's go ahead and run this.
11:36It's going to give us how long that person has been alive.
11:40So Luke Skywalker was born in 1977.
11:42He's been alive 48 years, 3 months, and 27 days.
11:47So these are both really good kind of built-in functions that you can use with the dates.
11:53Another really useful thing, and let's come right here, is we can do extract.
11:57Now, extract pulls different parts out of the date.
12:02So let's say we want to extract.
12:05We're going to pass through birth date after we specify the measurement that we're looking for.
12:10So we're going to say year from birth date.
12:14So we're just extracting the year now.
12:17So now we have the year pulled out.
12:19There's 1977.
12:21There we go.
12:22We're going to do the exact same thing, and you can guess it.
12:26We're going to, whoops, I need a comma there.
12:28We're going to do this for month.
12:32And let me put that all caps.
12:34Month and day.
12:36Now we can run this.
12:39And here's our month we're extracting, and here's the day that we're extracting.
12:43So we have 9, 25, 9, and 25.
12:47So we're just extracting information out of the already existing date column.
12:52Now this is starting to get a bit much.
12:55Let's come down here, and I'm going to just keep this information, actually.
13:01So let's go ahead and run this.
13:03The next one that I want to show you is intervals.
13:06Intervals are really important.
13:07Essentially what they do is they let you specify how much you want to add or subtract from a specific
13:13day.
13:14So what we can do is we can take birth date, and let me actually just bring this down to
13:19another line.
13:20So we're going to take birth date, and then we're going to say plus, and I'm going to say,
13:24when did this person turn 10 years old?
13:26Or when will this person turn 10 years old?
13:28So we can say interval, and then we're going to specify 10 years.
13:33So we're doing an interval of 10 years from this birth date.
13:39Let's go ahead and run this.
13:40So now we have 1977, 925, 1987, 925.
13:46These intervals can be really customizable.
13:48So let's come down here, and we'll do another one.
13:51We can do an interval of 10 days, for example.
13:54Let's go ahead and run this.
13:56So now this is 10 days later.
13:59You can see 925 goes into the next month of 1005.
14:02Now the very last one that we are going to take a look at, and we can keep it in
14:06this one, why not, is truncate.
14:08Now truncate is kind of like a rounding function for a date column.
14:14If we do date underscore trunk, let me spell this right.
14:18If we do date truncate, we can pass through the measurement that we're wanting.
14:23So this will be right at the beginning.
14:25We're going to say year, and then we'll do a comma, and we'll have the birth date.
14:30Let's go ahead and run this and see what it looks like.
14:32So for this, this person, Luke Skywalker, was born in 1977.
14:36So was Leia.
14:37They were born on the same day.
14:38Shocking.
14:39But if we come over here to the date truncated, it is going to the very first day of that
14:46year.
14:47So essentially rounding down to the year that they were born, or the very first day on the year that
14:52they were born.
14:53We can do the exact same thing.
14:55Let me get that comma in there.
14:56We do the very same thing, but we could specify we want the month.
15:00And let's go ahead and run this.
15:03So now it's rounding down to the month.
15:05So their birth date was 925.
15:08Now it's going to 901.
15:10So those are a lot of the really popular string and date functions within PostgreSQL.
15:16If you already knew all of these, you're a rock star.
15:18I don't know why you're watching this.
15:19But if you didn't know all of these, I'm glad that you're learning them because they're so, so useful.
15:24If you learned anything, be sure to like and subscribe.
15:27And I will see you in the next lesson.
15:40I'll see you in the next lesson.