LIKE Operator in PostgreSQL

Prathap G
A free video tutorial from Prathap G
Technical Enterprise Architect
4.1 instructor rating • 3 courses • 10,915 students

Learn more from the full course

PostgreSQL Bootcamp: SQL and PostgreSQL Database Masterclass

Become a Database Guru from Basic to Advance SQL PostgreSQL queries. Use SQL queries for Data analysis and Visualization

05:56:53 of on-demand video • Updated June 2019

  • How to Install PostgreSQL and PgAdmin 4
  • Retrieve the data using SQL queries.
  • Insert, update and delete data from tables in PostgreSQL.
  • Filtering, Grouping and slicing the data from the tables in PostgreSQL.
  • Various Built In functions in PostgreSQL.
  • Learn Data conversion functions in PostgreSQL.
  • Step by STEP approach in constructing a complex SQL queries.
  • Learn how to Join the data between various data sets.
  • Learn all the SET Operators in PostgreSQL.
  • Use the real Time NORTHWIND data set in the queries.
English [Auto] Hey guys. Welcome back. In this lesson we're going to learn a new operator or like operator like operating is one of the powerful operator which is often used in the weird class to search for a particular search pattern within a text field like operator is used to match text values against a pattern using a white box if it matches the search condition it is going to select that particular record. And if it doesn't match the search condition it is not going to select that record. How do you create the pattern the pattern within the like operator is created using two white cops a first white card is both sets sign and the second wildcard is underscored what does this do. Whenever you use a person's scent in a like operator it is going to select either the zero one or multiple characters. Similarly whenever you use underscored white card in a like operator it is always quick to select only one character. If you're finding it difficult to understand this then good buddy I will show you that usage of wild card characters using examples. Let me dive into the two let me execute this simple query which is going to select the data from the table products. So this is the table which contains the information of all the products that are being sold in the company the product name column contains all the product name information and similarly all the other related information is stored in other columns. And if it's called to my right there is a column called category which contains that category to which the products belong. Now let me show you a simple example of a like optical in this case. User is interesting to pick all the categories which starts with the letter B B In this case is capital. So how do we write this. It is very simple. You select that data from the products table and you feed them the red cards using bear claws. I going to do well like operator and the like operator is on the column category. And that search pattern other text pattern that I'm searching is Captain Lee. And then the Post said the wild card character if you carefully look here I'm searching for the categories using like operator which starts with Let that be. And since I have given the person sign as I've already told you it is going to be either a 0 1 or many characters that means that whatever that follows after B would be picked and returned to the user. And let me run this query like this crawl to my right now if you see the outboard it has picked on that record who's category value it starts with the B. In this case it has picked dried fruit and nuts and also baby products because both of this category it starts with a little B. So this is the simplest form of like operator where I will use the person sign at the end. Let me show you one more example. What do you think would happen here. The only change that I have made here is that I've used the person saying at the beginning and every piece capital D with smallness so what it searches is it is going to search the products whose product name ends with letter s since I have you and the person sign other person the wild card at the beginning it is going to search all the letters and then it is going to search for those specific product names that ends with letter s because I have given the character as at the end in the search condition let me in on this. Now you see that it has returned the product names whose name ends with S like this. Let me now show you one more example. What do you think that this could be done. Here I've used the the wild card both in the beginning and at the end and even the condition that's tried. So what it does it is going to search for the product name that has the word right anywhere in the product name it could be at the beginning or it could be at the end or it could be somewhere in between. Let me in on this query you see that it has to the products whose name is not when traders cry appears not to be in theaters right. IPods and not when traders cried plums. I hope you understood how we can speak the record since I have used a person at the start. It does match the condition not with traders and the space that follows after traders. And then since I have given right e heard it has selected the. It has selected the word right and seems again I have given the person after right any character that follows that right will be picked as well. In this case it has picked three records. Okay guys I have shown you three examples where I've showed you how to use person at the beginning and the posted wild card and then the usage of both said wild card both at the beginning and at the end. Let me show you one more example but this time I'm going to use another table called employees. Let me run this simple we see that this table contains the information of employees. Let me show my next example. What do you thing that this query would return. That is I've selected that data from employees with e-mail address like I've given five underscores and then I did it. And then the percentage point cut as I've told you before. Like operator loses to wildcard. One is the underscore and the other one is percent white card of Hughes to file underscored wild card and then the wild card. Plus I'm saying at the end as I told you earlier underscored with always select only one character. So what does it do here. What it is doing here is that since I have used five underscored wild card it is going to big fire characters from the beginning and then soon s given the value as at the rate of six character in the e-mail editors should be updated. And since I have given the percent white guard it is equipped to select all that data after activity. It will only pick those e-mail address was first name length is five characters and the sixth letter in that email address is buried. Let me run this query. Once you get the output I think it is going to clarify all the goals if you see the output it written only two records. As I've told you that my sixth character should always be attended. If you see here my dear it is the sixth character in this e-mail address in a second e-mail address my sixth character is actor ever since have you and five underscores it has picked on the e-mail address which has five letters before and that it is here to speak Nancy and Laura. These are the two e-mail address that satisfies this condition. Let me show you one more example. Why do you think this quality would do. This is very simple isn't it. I think in this case the second character in that e-mail should be. That's what it does. Let me run this query if you see the output it has picked. All the e-mail address. Second letter is a Nancy Jane. Maria and Laura let me show you one more example of what do you think this quarry would do. This query would pick the e-mail address or second category C and also its fifth character. Is it because I've used underscore and then the value at the second and fifth position. Let me run this query so there is one new one bright card which matches this condition. If you see this e-mail address lot up the second character in the name is a and also its fifth character is it seems to have used a person after the fifth character it has picked all that data after the fifth character. Now let me show you the last example. This is the same query which have used previously the only difference is that I have removed the both said character of the Post saying the wild card at the end. What do you think would be the output here. I want people to think about it before running this. Just pause the studio for a minute and understand the search condition and see what is the expected output. OK let me in on this equity this. Did not return any data. Why do you think so. The reason being is that there isn't any e-mail address length. There's only five letters and a second letter and a few sentences. There was one only one e-mail address. Secondly privacy. And the fifth clip was it. That is locked. So it matched the first five conditions as you have seen here that since we have not put any put said wild card you'd expect study to have only five letters in the e-mail address. Whereas the Laura had lot of other letters after its fifth character and because of that it didn't select the data. I hope they now make sense the usage of underscore and percent character. Okay guys like operators extensively used in the real condition. I want you to try out different combinations based on the search condition that you want with Doc. Let me wind up this lesson and see you all in the next lesson.