All language subtitles for 3. LIKE

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