(pleasant music)
(mouse clicking)
- What we're doing pattern matching over strings and SQL,
we often go to the like operator first,
but there's another operator called 'similar too',
which is even more powerful,
that allows us to use regular expressions.
Plus let's look at an example, working with job titles here,
(keyboard typing)
and I'm going to select
all the distinct job titles. (keyboard typing)
And I'm going to select that from the data size schema,
and the employees table within that schema.
And what we'll notice here
is we have a few different kinds of VPs,
like VP of sale, VP of quality control.
(breathes heavily)
We also have a number of different web,
like web developer,
and there's some other web designers in here,
web designer three.
So we have some patterns around VP and around web
that I think would be good candidates to work with.
So let's start with something simple.
Let's select all the VPs.
(keyboard typing)
Well, we could use the like operator here
and we can say where job title,
like
VP followed by any number of characters.
And if I execute that,
I'm going to see that we have five types of VPs.
Now, if I also wanted to include in this list,
anybody involved with web like a web developer
or web designer,
I could use an or clause
(keyboard typing)
and specify additional conditions
like job title,
like web percent.
And now I'm going to get a list of VPs
and web developers and web designers.
Well, as you can imagine, if you have a list,
a fairly long list of different patterns you want to match
using or clauses could get a little cumbersome.
A more succinct way of expressing the same condition
is to instead use the similar to clause
(keyboard typing)
instead of the like clause.
And the way we can do that,
is we can specify we want to match on a VP
followed by any number of characters
or
(keyboard typing)
on web followed by any number of characters.
And to specify the or operator I use a single pipe.
So that's the single pipe
and spaces are matched. (keyboard typing)
So I'm going to make sure that this is the correct string.
And because this is a list
I'm going to wrap it in parentheses.
So what this is saying
is exactly what the last command said,
which is to select a distinct job titles from employee
where the job title matches on VP
followed by any number of characters
or web followed by number of characters.
So if we execute, we get the same list,
which is what we'd expect.
Now, a moment ago,
I intentionally remove the spaces that I had put in there.
Now, typically I like to use a lot of white space
because it helps me read code a little more easily.
However, this white space is within a string.
There's those two quotes.
So that means it's going to become part of the pattern
that will be matched.
So this pattern,
the first one would be VP
followed by any number of characters.
And then the last character of the string is a space.
In the case of the web pattern,
the pattern that would be matched here is a space
followed by W-E-B followed by a number of characters.
So let's run that.
Okay, we don't get any results.
And again,
that's because the non printing characters
have become part of the pattern that we're matching.
So we want to be careful
with how we use
(mouse clicking)
non printing characters,
especially around things like spaces and tabs.
So if we run this again, we'll get what we expect.
Okay. Now let's look at just VPs.
(keyboard typing)
So here, I'm just going to work with VPs for a moment,
and I'm going to intentionally make some mistakes here
because that'll, again,
that kind of helps us understand
how regular expressions work.
So I would expect to get all five VP types.
And we did.
Now, let's say I'm interested in only VPs
of accounting, administration
and any other departments or divisions
that begin with the letter A.
So what if I have VP
and I want to say,
I'm just going to match VP
followed by any number of characters,
and then I want to match on an A.
Well, that didn't work.
What I'm trynna think what went on there?
Well, again, this whole thing,
this whole entire pattern has to match.
So unlike regular expressions in some programming languages
where this could match on a sub string
and be true in SQL,
the pattern has to match the entire string.
So what I am not saying
is what happens after the letter A
and what I want is really anything could follow
after the letter A.
So here, I'm saying string starts with VP
followed by a percent followed by an A,
followed by a percent
and the percent remember matches
on any number of characters.
So that's basically giving me anything that starts with VP
and also has an A in it anywhere.
And actually all five names of VPs have an A in it.
Well, really what I want is to match VP,
followed by a single space,
followed by an A.
I could put in,
(mouse clicking)
VP space a and execute that,
and that will match.
Now, I could also say VP
(mouse clicking)
followed by any character,
and I can specify that by using the underscore,
and this will also match on VP accounting,
and let's do a quick
(keyboard typing)
check at those five.
And let's say if I wanted something that started with an A
or an M, I could have VP,
(mouse clicking)
and I want to follow by a space
and I want to match on either an A or an M. (keyboard typing)
Now, if I ran this,
I'm not going to get any results,
because again,
forgot to put
(keyboard clicks)
the percent sign at the end,
which says match on any number of characters,
following the A or following the M.
And here, what we see is we're getting what we expect now,
which is the VP of accounting and VP of marketing.
So there were other things that you can use
within regular expressions,
other pattern, indication, specifications.
So for things like matching a particular character
a certain number of times for matching on digits,
I'd look at the Postgres documentation
to see how you can build even more complex,
regular expressions.
And my one piece of advice with regular expressions
is to build them incrementally.
So start with something really simple and start building.
And when things, all of a sudden aren't behaving,
the way you expect, you can isolate pretty quickly.
What change you made that is changing the behavior
that you don't quite understand.
(upbeat music)