Skip to Main Content

Write Great SQL Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

March 15, 2022

14:00 UTC   Start Times Around the World

Subscribe to be notified of changes to sessions and give us feedback!

Having trouble watching the video on this page? Open the video in your browser.


Visualizing SQL Pattern Matching (MATCH_RECOGNIZE) - A Beginner's Guide
Added in 12c, SQL pattern matching make it easy to search for sequences of different events across rows.

But how does it work?

Watch this presentation for a complete beginner's guide to match_recognize. With visualizations to show how it selects the rows, this session will teach you how to use this powerful clause.

Highlights include:

02:40 - An overview of match_recognize & its clauses
08:40 - Demo of using pattern matching with one variable to find consecutive 1km runs
17:00 - Comparing results when searching for different patterns of 1km runs
27:15 - Demo searching for two variables - 1km run followed by a 5km run
35:00 - Displaying overlapping patterns in the input
39:00 - Does pattern matching replace the where clause?
42:30 - Comparing different patterns outputs when using two variables
44:30 - Classifying rows into value ranges - the limitations of regular expressions
48:30 - Demo using pattern matching to group rows into range values and calculate totals
58:10 - Why use pattern matching - example use cases

Your Experts

    Chris Saxon

    Chris Saxon   

    Chris Saxon is an Oracle Developer Advocate and quizmaster on Dev Gym. His job is to help you get the best out of the Oracle Database and have fun with SQL! He's also part of the answer team on Ask TOM and creates YouTube videos at The Magic of SQL channel.
    Christian Berg

    Christian Berg   

    Christian Berg currently works as a Principal Analytics Domain Specialist for EMEA. His background is 20 years in consulting, evangelism and advocacy around Analytics, holding the title of Oracle ACE Director and delivering over 120 speeches across more than 50 conferences.