Original subtitles

0 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