Exploring REGEXP_LIKE in Oracle: Making Pattern Matching Fun!

 Hey there, fellow DBAs and SQL enthusiasts! Today, let's dive into the magical world of REGEXP_LIKE in Oracle. If you're familiar with the LIKE condition, you know it’s great for simple pattern matching. But what if you need something more powerful? Enter REGEXP_LIKE – it’s like LIKE on steroids, bringing the power of regular expressions to your SQL queries!

What is REGEXP_LIKE?

REGEXP_LIKE is used to perform regular expression matching, which is far more versatile than the simple pattern matching provided by LIKE. It evaluates strings using characters as defined by the input character set, making it an invaluable tool for complex searches.

Let's explore some fun examples with our good old scott.emp table.

Sample Data

First, here’s our sample data for reference:

SQL> SELECT * FROM scott.emp;

 EMPNO | FIRST_NAME | LAST_NAME | SALARY | COMM | HIREDATE
-------|------------|-----------|--------|------|----------
   101 | Scott      | Tiger     |   2000 |   10 | 30-APR-24
   102 | Ayush      | Maan     |   2000 |   10 | 30-APR-24
   103 | Radha      | Krishna   |   2000 |   10 | 30-APR-24
   104 | Ram        | Preet     |   4000 |   10 | 30-APR-24

Example 1: Finding Names Starting with 'R'

SQL> SELECT * FROM scott.emp WHERE REGEXP_LIKE(FIRST_NAME, '^R');

 EMPNO | FIRST_NAME | LAST_NAME | SALARY | COMM | HIREDATE
-------|------------|-----------|--------|------|----------
   103 | Radha      | Krishna   |   2000 |   10 | 30-APR-24
   104 | Ram        | Preet     |   4000 |   10 | 30-APR-24

Example 2: Names Starting with 'R', 'a' in the Middle, and Ending with 'm'

Next, let’s get more specific. We want names that start with 'R', have 'a' in the middle, and end with 'm':

SQL> SELECT * FROM scott.emp WHERE REGEXP_LIKE(FIRST_NAME, '^R(a)m$');

 EMPNO | FIRST_NAME | LAST_NAME | SALARY | COMM | HIREDATE
-------|------------|-----------|--------|------|----------
   103 | Ram        | Preet     |   2000 |   10 | 30-APR-24

Example 3: Names Ending with 'h'

What if we want names that end with 'h'? Simple:

SQL> SELECT * FROM scott.emp WHERE REGEXP_LIKE(FIRST_NAME, 'h$');

 EMPNO | FIRST_NAME | LAST_NAME | SALARY | COMM | HIREDATE
-------|------------|-----------|--------|------|----------
   104 | Ayush      | Maan      |   4000 |   10 | 30-APR-24

Conclusion

As you can see, REGEXP_LIKE opens up a world of possibilities for pattern matching in SQL. Whether you need to find specific strings or patterns within your data, REGEXP_LIKE has got you covered.

Feel free to tweak these examples and play around with REGEXP_LIKE in your own databases. It’s a powerful tool that can make your SQL life a lot easier and much more fun!

I hope you enjoyed this little adventure into the realm of regular expressions with Oracle SQL. Until next time, keep those queries sharp and your databases running smoothly!

Happy querying!✨😉