Okay, speaking a little bit more on like wildcard matches.
So if you don't want to use
a direct equivalency operator like a charity name is equal to a Kaiser,
what you can do here is you could use a wildcard operator for in this case,
matching on a string where it contains the word to the letters help.
H E L P, somewhere in the charity name.
As mentioned before, if you don't want to worry
about whether or not it was capital H or lower case H,
we're going to wrap the entire condition in the lower function here.
So, all the names that are being passed
into that lower function are to be converted to a lower case,
and we're matching them against that hard coded string value of help.
And the print, sorry the percentage signs there mean,
any number of characters can come before the H,
and any number of characters can come after the P in help.
If you want to just say one character comes before,
or one character comes after it,
you would use the underscore wildcard placeholder.
That's a single placeholder.
So let's look at the results.
So the results here's a lot of charities with help in the name.
Now you might look at the top three and go, oh come on,
like that doesn't necessarily,
that's not help, right?
And well according to your conditions it was.
Any number of characters could come before,
and any number of characters could come after,
concluding help itself nested inside of another word.
This time might want to get more specific.
Now we would say, alright,
well it has to begin with help.
So you remove that wildcard operator in front of the H,
and now you get everything that just begins with
H E L P. So a lot of
powerful things that you can do with
filtering and a lot of these SQL functions and operators.
So you build your arsenal,
the more practice that you get like the practice that you get to get in this lab,
or the practice the use and other public big core datasets,
is going to pay you dividends in the long run.
Getting familiar with what are some of
the different things that you can do with the data.
And the good news about doing this inside a big core is it will execute
very fast and you can move on to your next query.
So these functions, statistical functions, analytical functions,
like window functions and partitions,
and user defined functions like doing things in Javascript,
we want to save these three topics for the later courses,
as we focus on building up more foundational concepts like,
processing, and transforming, and cleaning up the data.
Mainly because I really want to show you the cool tool that is Cloud Dataprep as well,
for doing a lot of these same processing steps for data,
but doing it through a cool UI.
So if you're interested in a lot of these statistical approximation, functions,
and data partitioning, stick around for
the second and third courses in the specialization.
Now looking back, this is one of the first modules we're
really got to dive into some of the core capabilities of BigQuery.
As you've seen, exploring massive datasets with SQL,
is a one of the things that BigQuery does best,
and that's what you're going have to master as a data analyst.
And be sure to use standard SQL mode,
and make best friends with that query validator.
Lastly, if you're looking for other new datasets to explore,
with even more pre-built examples of SQL queries,
search for BigQuery public datasets to see the full list.
Now let's test your knowledge with an interactive lab.
We've written some pretty ugly and broken queries for you to fix on our IRS dataset.