(mouse clicking)

(滑鼠點擊)

- What we're doing pattern matching over strings and SQL,

- 我們所做的是對字符串和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.

VP後面有任何數量的字元。

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

是我們可以指定我們要在一個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.

而為了指定or操作符，我使用了一個單管。

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.

後面是W-E-B，後面是一些字元。

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.

以字母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.

然後我想在一個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,

並在SQL中為真。

the pattern has to match the entire string.

該模式必須與整個字符串匹配。

So what I am not saying

所以我沒有說什麼

is what happens after the letter A

是發生在字母A之後的事情

and what I want is really anything could follow

而我想要的是真正的任何東西都可以遵循

after the letter A.

在字母A之後。

So here, I'm saying string starts with VP

所以在這裡，我說字符串以VP開始

followed by a percent followed by an A,

後面是一個百分數，後面是一個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

是以，這基本上是給我任何以VP開頭的東西。

and also has an A in it anywhere.

而且還在任何地方有A。

And actually all five names of VPs have an A in it.

而實際上，所有五個副總裁的名字中都有一個A。

Well, really what I want is to match VP,

好吧，實際上我想要的是與副總裁相匹配。

followed by a single space,

後面有一個空格。

followed by an A.

後面是一個A。

I could put in,

我可以放進去。

(mouse clicking)

(滑鼠點擊)

VP space a and execute that,

副總統空間a並執行。

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

比方說，如果我想要以A開頭的東西

or an M, I could have VP,

或M，我可以有副總裁。

(mouse clicking)

(滑鼠點擊)

and I want to follow by a space

我想在後面加一個空格

and I want to match on either an A or an M. (keyboard typing)

而我想在A或M上進行匹配。

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.

在A後面或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

我想看一下Postgres的文檔

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)

(歡快的音樂)