Original subtitles

0 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