SQL: Find Years using RegEx or LIKE

This quick guide helps you find years in any SQL table or column.

Sometimes you're looking for a specific year in a SQL column, but how do you find them with a single, simple SQL query?

Find Years in SQL using Regex

This is an example SQL query for finding any years — simply searching for four digits in a row — and returning all rows. Here's the query:

SELECT *
FROM table
WHERE column REGEXP '[0-9]{4}'

If you want to make this query work in your SQL database, simply change table to your corresponding table, and column to the actual column you want to search.

As another example, if your table is called books and you want to search the titles column for years, this is how you'd do it:

SELECT *
FROM books
WHERE titles REGEXP '[0-9]{4}'

Hope this helps!

Example Without Regex

If you don't want to use Regex to find years in your SQL queries, you can also use the LIKE operator. This is an example of finding years using the LIKE operator:

SELECT *
FROM media_titles
WHERE mt_title LIKE '%[0-9][0-9][0-9][0-9]%'

This might be easier to read, but it won't be as fast to execute and also results in a slightly longer query.

Using Regex in SQL

Regex is rather heavy handed and hard to learn, but sometimes there simply is no alternative. I will continue adding more posts using regex in the near future.

Details

• Title: SQL: Find Years using RegEx or LIKE
• Published: November 21, 2024
• Author: Andrew Young
• Categories: SQL Guides