All language subtitles for 2. ADVANCE PATTERN MATCHING (REGULAR EXPRESSIONS)

Now let's discuss different wildcards of regex expression
1

There are multiple wildcard, we have only selected few important wildcard
2

The first one is the or operator
3

With this pipe symbol you can denoted it as a Or operator
4

The second is the asterisk sign
5

asterisk denotes repetition of previous item 0 or more time
6

Then there is a plus symbol
7

Plus denotes repetition of previous item one or more time don't worry we will discuss this
8

In more detail in our example
9

Next is a ?
10

Question mark denotes repetition of previous item 0 or 1 time
11

Then
12

There is a curly bracket and in curly bracket we can mention any number and this denotes
13

repetition of previous item exactly m number of times
14

So for example if you want a character a
15

To come exactly three times
16

You will denote A
17

Curly bracket 3
18

And if you want A to come m times or more than that then you will write curly brackets
19

M
20

Comma
21

This denotes that the repetition of previous item M or more time
22

Similarly if you want to apply upper limit and lower limit on the repetition you will write
23

Curly bracket m, n
24

Now as I told you earlier tilde operator will match only a part of string
25

Now
26

To denote the start of a string you have to write carat(^) or hat symbol
27

And to denote end of the string you have to write ampersand($) symbol
28

In regular expression you can also use character sets
29

So you can mention all the possible characters in square bracket
30

And SQL will treat it as or operations, so for example if I write
31

Square bracket ABC
32

That means that
33

That place can contain a or b or c
34

Similarly there are short hands also so for example if you want to mention all the characters
35

From A to Z you can just write square bracket
36

A then dash then Z
37

Similarly if you want to mention numeric characters you can write
38

1-9
39

Now the operator to match using the regular expression is tilde sign
40

But tilde sign is case sensitive
41

You can also use tilde and then an asterisk sign
42

To make it case insensitive
43

Now let us look at some examples
44

Suppose we want to identify all the customer where customer name start with a
45

In this case
46

We will write, tilde then * sign that means we are matching using case insensitive
47

Operator
48

Then like any other string we will start mentioning the rules using a single quotation, then we have to
49

to mention the start of string that's why we will mention a ^ symbol
50

And my first character should be A
51

That's why I am writing ^ symbol then character a
52

And this A can repeat itself
53

Either one time or more time
54

That's why I have to write plus symbol
55

Now after my first character there can be anything
56

So I am mentioning the character set, my character set contains all the alphabets of English that is
57

A to Z
58

And it also contains a space, since there is a space between first name and last name
59

That's why I have to write space also
60

So what this means is my second character can be anything from A to Z
61

Or it can also be space
62

No I want to replicate same rule for other positions as well
63

That's why I have to use plus symbol
64

And then have to mention the end of a string
65

That's why I am writing $ symbol
66

Do note that the symbol for space is backslash s \s
67

Similarly symbol for dot is backslash dot
68

And symbol for
69

Dash is backslash dash, So for all the special characters you have to write backslash before
70

Them
71

So let's go to PG admin to write this
72

So you will write select star from customer
73

Where
74

Customer name
75

Then a tilde operator with asterisk sign to make it case insensitive
76

And then in the single quotation I will write hat
77

A
78

Just to denote that string is starting with A
79

Then this A can repeat itself n number of times
80

So I am mentioning + sign
81

Then I am defining the character set which contain all the characters from A to Z
82

And a space
83

Now the characters of this character set
84

Can repeat itself N number of
85

Time that's why I am mentioning a Plus sign
86

And then I have to mention the end of a string
87

That's why I am mentioning & sign
88

Let's run this query
89

So right now we are not getting our desired result
90

So we have to change this / to backslash
91

And then again rerun the code
92

Now you can see that all the customer names are starting with A
93

And after that there can be N number of characters between A to Z or space
94

Now suppose
95

In the second example, we want all the customers whose first name is start with a b c
96

Or D
97

So in this example we will use the or operator
98

Will provider pipe operator between a b c and d and put this
99

In a parentheses
100

We can also use character set of ABCD instead of this or operator and parentheses
101

So let's write this query in pg admin
102

Will write select star from customer
103

Where
104

Customer name
105

Then the tilde operator
106

With asterisk sign to make it case insensitive
107

And then in single quotes I will write
108

Start of the string then ABCD in the parentheses with
109

Or operator
110

Then this can repeat N number of time that's why I will put
111

Plus symbol
112

Then in the next character set it can take any value from A to Z
113

And a space as well remember to put backslash as for space
114

And then the + sign
115

For the repetition
116

Then I will end the string
117

And
118

Run the code
119

You can see we have all the customer whose first name starts with either A or B or
120

C or d
121

Remember if you want to write same query using like we have to use four like statements
122

With regex we can do this in a single query
123

Now
124

in the third example
125

I want the first name and the last name of my customer to be exactly of 4 character and the first name
126

Name should start with either A B C or d
127

To do this
128

I will write the start of the string then I will mention a b c d using or operator
129

Then here I will not write plus symbol because I want exactly One character
130

After that I will write a to z
131

This is the character set
132

And I want this character set to repeat exactly three times
133

So my first character should be ABCD
134

And the next three character Can Be Anything between a to z
135

And after this first name I want space in between
136

That's why I will mention backslash s
137

And after that I want my last name to contain exactly four character that's why I will
138

Write A dash Z, I am mentioning the character set and I want this character set to repeat
139

itself 4 times
140

So let's
141

Write this example in PG admin
142

Mention the start of string I will write ^ symbol after that I want my first name to start with
143

A B C or D Audi that's why I will write all this characters with
144

Or symbol or pipe symbol
145

After that I want any character between A to Z and wanted to repeat 3 times
146

Therefore I will put 3 in the curly brackets after that I want a space
147

So I mentioned backslash space after that I want a to z
148

4 times to ensure that my last name should contain only 4 characters
149

So I will mention four in the
150

Curly brackets after that I want my string
151

To get over so that's why I will put $ symbol
152

Now let's run this
153

You can see
154

We are getting all the customer
155

Who is first name and last name contains only four characters
156

And
157

Customers
158

Where
159

First name are
160

Starting with A B C or d
161

Now suppose
162

We have some email IDs in our customer name
163

And for this example we are using another table that is the users table we have already loaded
164

Data into this table
165

For your practice we have provided a Notepad file containing the queries to create this table
166

In this table we want to identify
167

Valid email ids
168

That are present in my name
169

So first let's go to
170

PG admin and view the table
171

Select star from users
172

You can see that we have only one column that is
173

Column for name
174

And in that column we have few email ids as well
175

So now we want to identify this email ID , only valid email ids
176

From this data
177

So a valid email ID
178

Should contain alphanumeric string it can also contain a dot
179

Or a Dash
180

Then there should be a @ sign
181

And then after that
182

There can be alphanumeric string
183

For the domain name
184

It can also contain dash as well
185

And after there that should be a dot
186

For example in gmail.com
187

There is a .com coming at the end
188

That's why
189

We are putting a condition on the dot and after that there can be 2 to 5 characters
190

That's why we are putting a to z
191

It should be between two
192

Or 5
193

So we are not accepting a single character and we are not accepting character length
194

More than 5
195

So let's
196

Write this in our PG admin
197

Select star from users
198

Where
199

Make it case insensitive I will write tilde star
200

Then under single quotes
201

To mention the first part of our
202

Email ID
203

We'll mention all the possible characters that is a to z 0 to 9
204

Dot
205

or Dash
206

Or underscore and we want this to repeat N number of time
207

After that we want
208

@ symbol and after that we want another character set
209

Of A to Z or 0 to 9
210

Or Dash since domain name contains only alphanumeric characters and can also contain Dash
211

And we want this to repeat n number of times
212

That's why we are putting
213

+
214

After that we want a dot to come after the domain name
215

That's why backslash dot and after that another character set of a to z
216

We want to repeat it exactly between 2 and 5
217

So this query will retrieve only valid email ids from a database
218

Let's run this query
219

You can see we are only getting
220

Two valid email ids
221

There was an email id that was starting with @
222

And we have filtered out that email id using this query
223

So regex expressions are very useful and you can use it in other advance string function
224

As well
225

SO that's all for this lecture