Original subtitles

0 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