All language subtitles for 4. TRIM, LTRIM, RTRIM

Next we will look at the trim functions
1

In postgres there are three type of trim functions one is normal trim
2

Other is a rtrim and there is ltrim
3

Trim can be used to replace all specified characters
4

Either from the beginning or at the end or both
5

RTrim will remove only characters from the right hand side of the string Ltrim remove only from the left hand side
6

So trim can do work of both Rtrim and Ltrim
7

Let us see how
8

in the syntax you can see
9

When you write trim
10

In the brackets
11

You have the option of mentioning leading trailing or both. By default it will be both
12

If you mention leading
13

It will function is Ltrim
14

That is it will remove characters from the left side of the string
15

If you mention trailing it will behave as rtrim
16

That is it will remove from the right hand side of the string
17

After mentioning leading trailing both
18

You have to mention the
19

Trim character
20

The character that you want to trim if you do not mention any character it will remove only spaces
21

If you know that it is a particular character
22

So for example if you have additional commas or any other particular character you can mention that character also
23

By default it will be a space
24

and then you mention the string
25

The syntax for Rtrim and Ltrim are a little different
26

You first mention the string and then you mention the trim character
27

so the
28

String comes first you then
29

Mention the character that you want to trim from that string
30

So trim will mainly be used to clean your data
31

For example if your data
32

Has been created by input from customers
33

Then it tends to have additional spaces in the beginning in the end
34

In such cases where your data is not that clean
35

You can use trim function
36

If you do not use trim function due to these additional spaces you will not be able to match the strings
37

So matching will also be a difficulty with unclean data
38

So trim will help you
39

Clean the data and if you further want to match the strings that will also be easier after doing trim
40

On the
41

column name
42

If you look at the examples
43

If I write select trim leading
44

This is the character space
45

From
46

This string this is a string Start-Tech Academy it has some spaces in the front some in the back
47

If you mention leading, it will only remove the leading spaces which are the
48

The spaces which are coming
49

Before the string
50

So it will remove only these
51

Spaces
52

If you mention trailing it will only remove these spaces not these spaces
53

If you mention both
54

It will remove spaces from both the sides
55

If you do not mention
56

Any of these
57

By default it will remove
58

From both the sides
59

If you even do not mention this character
60

It will still remove the spaces because default character is space
61

If it had been any other character you need to mention
62

that particular character
63

For Rtrim
64

Again when we write this it will remove spaces from the end
65

Ltrim will remove spaces from
66

Left end
67

So let us go and write all of these
68

So we will first trim
69

The leading so it will be leading
70

then we'll mention the
71

Space character within single quote
72

Then we will mention from
73

And then we will give the string
74

This can be a string or you can mention a column name also so this will remove
75

All the
76

Leading spaces from that column values
77

So let us run this query and see
78

So if you
79

expand this
80

You can see
81

There are extra spaces after Academy but the spaces in front of start have been removed
82

If you change the leading
83

to
84

Trailing
85

Leading to trailing
86

and you run this
87

the spaces in the front are still there
88

But
89

Spaces in the end have been removed
90

If you write both
91

Spaces from both the sides will be removed
92

Let's run this
93

Double click you can see
94

Spaces from both the sides are removed
95

If you remove both also and the special character also
96

It will still Run
97

With the default values of both and space and
98

it'll still give us the same result
99

So you can see
100

we still have the same result when no extra spaces on
101

Either sides
102

Next we'll look at Rtrim
103

Select
104

Rtrim
105

First mention the
106

String which is Start-Tech Academy with spaces on both the sides
107

Then after comma mention the
108

Character to be
109

trimmed. let's run this
110

So you can see that only the right spaces have been removed, the left spaces are
111

Still there
112

If you write Ltrim on this same
113

string
114

Just copy paste it
115

use Ltrim
116

You'll notice that the spaces in front will be removed
117

Spaces in the end are still there
118

So this is Trim, Ltrim and Rtrim for you
119

Just to repeat that trim is a very important function to clean your data and to match those textual
120

columns
121

So whenever you are joining or matching text using the where clause
122

Do remember to use
123

Trim or the upper lower functions whenever you do not have the standardized data