All language subtitles for 1. PATTERN MATCHING BASICS

Hello everyone
1

Welcome to our bonus lecture on pattern matching in postgres SQL
2

In this lecture we will start by discussing different types of pattern matching, their syntax
3

And then we will discuss few examples of each type
4

At the end, we will also give you some tips on the usage of different methods
5

So let's start
6

We can define pattern matching
7

As a method to identify the string complying to given format
8

Suppose, we want to identify all the customer name that starts with letter A
9

Or we want to identify the customer who has provided email id instead of names
10

Or
11

To identify the customer who has not provided the domain names in their email ID
12

We can find all such customers using a pattern matching in postgres SQL
13

This lecture will set your fundamentals of other advanced string functions as well
14

Such as string split
15

Etc.
16

So understand the concept thoroughly and you will be able to use other functions as well
17

In postgres SQL there are
18

Three methods to perform pattern matching
19

First is the like statement
20

Second is the similar to statement
21

And third is using Tilde operators with regular expressions
22

Which are also known as regex expression
23

We have already discussed like statements in our previous videos
24

And we will be only covering few examples
25

To refresh our memory
26

Now moving on to similar to function
27

These are the SQL standard functions
28

And the only reason postgres SQL support it, is to stay compliant with SQL standards
29

And Internally
30

Every similar to expression is written in the form of regular expressions
31

And therefore there always be a regular expression to do the same job faster
32

Then the Similar to statements
33

So there is no point in discussing similar to expressions
34

And you should also avoid it in your queries and try regular expressions instead
35

Regular expression with tilde operator provide us a very powerful and flexible tool to perform pattern
36

Matching
37

And one thing to note here is that the wild cards of like statements and wildcards
38

Of regular expressions are different
39

And you should try to learn them separately
40

In this video, we will be mainly focusing on the regular expressions only
41

Another major difference between like a statement and regular statement is
42

Like statements perform pattern matching on the whole string
43

Whereas regular expressions perform pattern matching also on the part of string
44

So suppose if I want to find customer name with just
45

Character A
46

Like statement will find the customer name where
47

There is only one character which is a
48

Whereas regular expression will find all the customers
49

Where the name contains a character A
50

So let's start with the like operator
51

There are two wildcards in like operator
52

First is the percentage symbol
53

And second is the underscore symbol
54

Percentage symbols allow you to match any string of any length
55

Whereas underscore symbol allow you to match
56

Only a single character
57

Let's look at some example
58

We have already discussed this in our previous videos
59

So in this video we will be only discussing it and not executing it in pg admin
60

So suppose if we want to find all the customers
61

Where the first name starts with
62

Character J and o
63

Will write select star
64

From customer table
65

Where first name
66

Is like
67

J o and then the percentage symbol, we are using percentage symbol because we don't know the length
68

Of the name and we want to identify all the customer names where the starting characters are J and o
69

Now suppose you want to find all the customers
70

Which contains letter O and d
71

So will write, select star from customer table where first name
72

Like
73

Percentage symbol
74

Then OD
75

Then percentage symbol this means that first name should contain
76

O and D adjacent to each other
77

Now in the next example suppose I want customer name
78

Which will start with J A S
79

And then there should be exactly One character
80

That can be anything and
81

Then there should be character N, in that case I will use underscore
82

Underscore will ensure only a single character replacement
83

We can also use not statements with the like a statement and the next example you can see that we have
84

Used not like J percent to identify all the customer
85

Whose names doesn't start with J
86

Now suppose
87

I want to identify all the customers
88

Whose names start with
89

A B
90

C or E
91

For this I have to write four different like statements with or statements in between
92

Now consider a more complicated case
93

Where
94

I want my first name to start with
95

ABC
96

D or E
97

And my second name should start with
98

F or g
99

In this case
100

I have to write 5 into 2
101

So total 10 like statements with the combination of or and and keywords
102

Now let's suppose I also want to constrain on the length of my first name and last name
103

In this case
104

I first have to identify the separator between the first name and the last name that is a position of space
105

Space
106

In my name
107

And then I have to write
108

Two More
109

Conditions on the length of first name and last name
110

So you can see with the increase in number of condition
111

And the complications
112

The number of like statement in SQL
113

Increases exponentially
114

In general, like statements provide quick and easy way to solve
115

Simple pattern matching problems
116

But for Complex matching problem we have to use the regex functions
117

You must be thinking, we will hardly encounter any such situation in our professional career
118

So Let me tell you an example
119

Suppose if you want to filter out invalid email ids from your data
120

So a valid email id should contain
121

A string of alphanumeric characters
122

With
123

Either dot
124

Or
125

Underscore symbol
126

Then
127

There should be a @ sign
128

And then again there should be alphanumeric string
129

For example Google or Yahoo
130

Then there should be a dot
131

And then after that dot, there should be 2 to 8 alphabet
132

Such as .com or .in etc
133

A valid email id should contain all of this parts
134

And we will learn how to write this using regex expression