In this blog we are going to see about Regular expression in Sql via examples in Crisp and Clear manner. Most of the sql query writing interview we can expect the Regular Expression Topic, but many of us will not be more confident on this topic. So, here is the blog which is going to be a saviour.
This blog is going to be a very basic level but covers the most of the topics that are required. So, lets get started.
Regular Expression is nothing but the pattern matching. We can filter the data in a table using Where clause it self, but to filter based on a particular pattern we need Regular Expression.
To make it simple, let us put up in this way, we need to add the keyword "REGEXP" after where condition to use Regular expression and we need to define the pattern over there.
SELECT name FROM table WHERE name REGEXP '^bh'
The above query will result in fetching the record where the name column values stating with "bh" .
The symbol ^ denotes the starting value. Example, it will fetch the name like "bharathan"
SELECT name FROM table WHERE name REGEXP 'ha$'
The above query will result in fetching the record where the name column values ending with "ha" .
The symbol $ denotes the ending value. Example, it will fetch the name like "Sushmitha"
Now let us see some of the basic rules.
Inside Square Brackets:
The characters given inside the square brackets are considered to be for the pattern matching , and remembers that any one of the character inside the square bracket is matched in the column value that record is filtered and given in output.
Please go through below examples to get clear idea and remember these and that is more than sufficient to answer many questions.
[abc]
Returns the records that matches the column value with any of the character mention in square bracket, that is a, b or c
[^ abc]
Returns the record that matches the column value with any character except a,b,c.
[a-z]
Returns the record that matches the column value with any lowercase letters. That is a to z.
[A-Z]
Returns the record that matches the column value with any Uppercase letters. That is A to Z.
[a-zA-Z]
Returns the record that matches the column value with any letters. That is (a to z) or (A to Z)
[0-9]
Returns the record that matches the column value with any digits. That is 0 to 9.
Quantifier:
Using this we can tell the computer what is the repetition. We are going to use this next to the square brackets [ ] , whatever inside the square brackets which will be repeated based on the Quantifier. Lets see about this,
[ ]?
The pattern inside the square bracket must occurs 0 or 1 times
[ ]*
The pattern inside the square bracket must occurs 0 or more times
[ ]+
The pattern inside the square bracket must occurs 1 or more times
[ ]{n}
The pattern inside the square bracket must occurs n times
[ ]{n,}
The pattern inside the square bracket must occurs n or more times.
[ ]{x,y}
The pattern inside the square bracket must occurs at least x times but less than y times.
It is also better to know about Regex Meta characters. Let us see about that.
Regex Meta characters:
\d
The above short form can be used instead of [0-9]
\D
The above short form can be used instead of [^0-9]
\w
The above short form can be used instead of [a-zA-Z_0-9]
\W
The above short form can be used instead of [^ \w]
Now let us practice a simple REGEXP with an simple example,
Example :
*) Select the records where the mobile number start with 9 or 7 and total digits =10
SELECT mobile FROM table WHERE mobile REGEXP '[97][0-9]{9}'
The explanation for the above example with regression [97][0-9]{9} is , Our requirement is the first number must be start with 9 or 7 so we used [97]. Note that we need total of 10 digits, out of which one we already decided. So, 9 digits are only remaining. Therefore it is followed by [0-9]{9}
Thus we saw about Regular expression in sql with crisp and clear explanation along with examples. Hope this information is helpful.
You can Follow this blog to show your support by clicking the Follow Button in top right side pane of this window. Also you can follow my Linked in Profile and any of the social media plugin provided on the right side pane of the window.
Thank You!!!
0 Comments