Original subtitles

0 So we have seen how to use 1

In condition and between condition 2

Now let us learn how to use the like condition 3

Like condition is used to perform pattern matching 4

Between string using wildcards 5

But by pattern matching I mean you can find if a string is starting with a particular character or is ending with 6

a particular set of characters 7

That kind of pattern matching can be done using the like condition 8

The syntax of like condition is 9

Select the columns you want from the table name 10

And when you specify the condition using where condition 11

You use like 12

And then you specify the pattern 13

To specify the pattern 14

We'll be using wildcards 15

As told earlier also, wild card is a special character, it is a symbol 16

And this symbol means something to SQL 17

For example when you are using wildcards with Like, there are two particular wildcard used 18

One is the percent symbol 19

Percent symbol allows you to match any string of any length 20

Whereas this underscore wildcard, it allows you to match only single character 21

For example 22

If you write A percent 23

It means 24

Any string with starting with A 25

There can be any number of characters in that string 26

Percent A will mean 27

That the string is ending with A 28

In front of A there can be any number of 29

Characters 30

A percent B will mean, it is starting with A 31

Ending with b 32

And between A and B there are 33

Any number of charachters, do note that a space is considered as a character 34

And 35

Even 0 36

I mean to say no character will also be acceptable 37

So, A percent B will also include AB 38

That is, there is no charachter between a and b 39

IN the fourth example I am showing you the use of underscore symbol 40

AB underscore C will mean 41

The string is starting with A and B 42

And it is ending with C and between these 43

B and C there is only one single character 44

So it is a 45

Four character string 46

Let us use this in examples 47

Try to find out 48

Pattern 49

Suppose we want to find out the customers 50

Whose name start with 51

J 52

To do that 53

This is the syntax 54

Select 55

Everything from the customer table where 56

Customer name 57

Is like 58

J 59

Anything after j, So it is J percent, let us go and write this query in SQL 60

select star 61

From customer 62

Where customer name like 63

GJ 64

Percent 65

Note that this J is in capitals 66

Names which are starting with capital J will be selected 67

So when I run this query you can see all the 68

Customers that I have now 69

Have there names starting with J 70

That is capital J 71

Now if you want to select 72

Customers 73

Who has Nelson in their name 74

Maybe their first name is Nelson, maybe their last name is Nelson 75

Or 76

Within their name this string 77

Nelson appears 78

How to find such customer 79

To do that 80

We'll go to PG admin and write 81

Select star from customer 82

Where customer name like 83

I will have 84

Percent 85

Nelson % 86

Run this query 87

There is only one customer 88

whose last name is Nelson 89

And that 90

Record we have found using this query 91

Now suppose you want to find out 92

Customers 93

Whose first name consists of four characters 94

And after that there can be any number of characters 95

How to find such a customer 96

We need to use the underscore wildcard 97

For the first name will use four underscore characters 98

We need a space 99

After that there will be a percent symbol 100

This percent symbol will ensure that there can be any number of characters 101

After the first name 102

So Let us do this write this query to find out the customers who is first name has 4 characters 103

Select Star from customer where 104

customer name 105

Like 106

one underscore 107

2nd underscore, 3rd underscore, 4th underscore and now a space 108

This will ensure that the first name ends after four characters 109

Now 110

A percent symbol 111

Let us run this 112

You can see we have got 113

All the customers whose first name is of four characters 114

And after that there can be any number of characters 115

In the fourth example 116

I'm showing the use of like 117

Condition along with the not condition 118

So if you want to find out all the cities which are not starting with the letter S 119

How to do that 120

In this condition will also be using the distinct 121

Keyword, distinct will ensure that the city names are not a repeating, since a lot of customers are residing 122

In the same city if I just write select 123

I'll get the 124

City names repetitively 125

So to get unique city name 126

I'll write distinct also so select distinct City from customer 127

Where city not like 128

And it is not starting with S so let's go and write this condition 129

Select distinct 130

City this is the column name from table 131

Customer where City 132

not like 133

That is not starting with S so S percent. Remember to put S in capital 134

Since our city names are starting with capital letters 135

So you can see, we have all the city and none of the city is 136

Starting with the S character 137

The last example 138

Is Using the escape character 139

This backslash 140

That we have here is called an escape character 141

It is used 142

To escape the meaning of percent as a wildcard 143

What I mean by that is 144

If you want to actually find out a percent symbol 145

In your data 146

How do you do that if you write the percent symbol it will be considered as a wildcard 147

If you want this percent symbol to be considered as a percent symbol. You'll need to use this backslash 148

Which is called an escape character, so if you 149

Run this query select star from customer where customer name like the backlash percent. It will actually 150

Find a customer 151

Which have a name of G% 152

G% 153

Since we do not have any customer like that 154

I just wanted to show you this example so will not be running this query that's all with this 155

Like condition, in the next video will learn how to order the 156

Retrieve data set 157

So For example, when we retrieve customer name it was coming in the random order 158

I want to get the customer names ordered in the alphabetical order 159

As Starting from A and ending with Z 160

How to do that, we willl learn that in the next chapter