MySQL Database Tutorial - 15 - Regular Expressions Video Lecture | MySQL (Sequential Query Language) Database Tutorial - Software Development

alright guys welcome to your 15th
tutorial in MySQL and in this lesson I
want to talk to you guys about something
called regular expressions now regular
expressions is basically something
that's built into MySQL and it's a way
that you can search for more complex
patterns other than just using the
wildcards % and underscore I meant to
type those % and underscore so it's kind
of like a language all on its own and
it's actually incorporated not only into
MySQL but a bunch of different other
programming languages like PHP and
JavaScript I believe so
learning regular expressions on its own
is a whole nother beast but for this
tutorial I just want to tell you guys
how to incorporate it into MySQL so let
me go ahead and let me go and select the
name of item from the table items where
the name and instead of hitting like I'm
going to type our EG e XP that means
that we want to work with regular
expressions now in single quotation
marks you go ahead and you type your
regular expression now the easiest
regular expression is basically just a
word for example knew what this regular
expression means just a symbol single
word is basically saying search for
anything that contains the word
new so whenever we hit go as you can see
it doesn't matter if it appears at the
beginning at in the middle or as part of
another word as long as it contains the
word new then there you go
so of course we can achieve this just by
using like in some wild cards but this
is how you incorporate regular
expressions now another thing that may
come in handy is the dot or the period
whatever you want to call it now the
period means any single character so if
you were to write something like da
boxes then if any single character came
before boxes in your term then that
would go ahead and hit any results so
for example a space came before all
these and before that excuse me
a number so that's why dot boxes matched
all of the
terms right here so of course we could
also do this now there are some other
regular expressions and I want to talk
to you guys about a few other ones the
next regular expression I want to talk
to you guys about is the pipe I forgot
it's called technically the pipe line
the pipe symbol whatever is it's the
little thing above the enter on your
keyboard when you hit shift it gives you
a straight up-and-down this is basically
the same thing as or in other words bow
are and basically when I'm saying it's
the same thing as or you can go ahead
and hit two words on either end of this
pipe sign for example gold pipe sign car
and what this is saying is basically
this search for any item that either
includes the word gold or the word car
it doesn't have to include gold car or
anything like that just one or the other
so basically the pipe sign is the same
thing as or so let me just go ahead and
take a look at our resort's traditional
carpet now even though this isn't like a
traditional car and car is separate as
long as it includes the word car or gold
somewhere in there so basically it's
like using parentheses on either side of
gold or car it just may be a little
quicker whenever you're coding so again
as you are seeing already regular
expressions are just using symbols in
really weird ways now the last regular
expression I want to talk to you guys
about is something called a set now a
set is basically like a list or a bunch
of different or statements now I want to
show you guys this because it can save
you guys a lot of time instead of having
to type a whole bunch of different or
statements now if you were let's say you
wanted to match a bunch of specific
characters like for example one two
three four five let's say we wanted to
find one boxes of frogs two boxes frogs
three boxes frogs four boxes frogs well
we can do that using a bunch of
different my SQL statements or we can
use a set in regular expressions now any
time you want to use a set make sure to
include it in square brackets
then type all of the different terms you
want to search for now make sure not to
include commas in between them it's not
like a list in MySQL so don't forget
that now go ahead and type boxes of
frogs and what this statement is
basically saying is okay search for one
boxes of frogs two boxes the frogs three
boxes four boxes or five boxes of frogs
so it's basically five different MySQL
or statements in one so now I'm just go
ahead and copy that and run and as we
can see it returns three boxes of frogs
now we also have seven boxes in four to
eight boxes but those then are returned
because they weren't in the set now
another cool thing that you can do real
quick is you can negate the set and then
negate a set means basically okay search
for anything except this list
so basically we're saying okay search
for any boxes of frogs but since we
included that little caret right there
and this is the symbol above the six on
your keyboard we're saying okay search
for any number boxes of frogs besides
one two three four and five so now
whenever we hit go that is when we get
48 and seven and again this is the gate
symbol that can be used with other
regular expressions to not only sets
again regular expressions are pretty
much taking all the symbols on your
keyboard that you thought you would
never use and putting them to use I
think I think that's what the creator of
regular expressions wanted to do he's
like wow since all of these crap you
know all this crap I don't use I'm gonna
put a use to it and that's how regular
expressions was probably invented and oh
yeah I wanted teach you guys one more
thing you know how I said that you can
include a set like 1 2 3 4 or 5 any time
you want set not only of numbers like 1
through 5 but you can use this with the
letters like a to Z you can go ahead and
just write 1 minus 5 like you're writing
1 through 5 and that's just a little
shortcut instead of you know having to
write the whole set so whenever you want
to do something 2 3 4 5 6 7 8 9 you can
just do 2 2 9 and that's the same thing
so if we go ahead and search this since
3 is in the range of 1 5
five that's just a little shortcut I
thought to tell you guys real quick so
again like I said there are a ton more
regular expressions it's like its own
language made up of special characters
and it really for me to cover all of the
regular expressions would be a waste of
time because regular expressions are
really outside the scope of these
tutorials they're just incorporated into
MySQL so that's why I wanted to show you
guys this story real quick because
whenever you come across them this is
how you put them and incorporate them
into minus QL so again I encourage you
guys to go online look at the list of
are the regular expressions and then
once you understand them learn how to
use them this is how you use them in
MySQL but for now thank you guys for
watching how to use regular expressions
in MySQL and yeah I'll see you guys in
the next video
oh and by the way don't forget to add me
on Google+