(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)