Filtering rows using regular expression in SQL, Python and R

Given a table or dataframe named students as shown below, get all the records from the table or dataframe where the country the student comes from contains the character “y” or “d” using regular expression.

| ---------- | ------------ | ------------ | --------------- |
| student_id | student_name | student_city | student_country |
| ---------- | ------------ | ------------ | --------------- |
| 1          | John         | Atlanta      | USA             |
| ---------- | ------------ | ------------ | --------------- |
| 2          | Hari         | Mumbai       | India           |
| ---------- | ------------ | ------------ | --------------- |
| 3          | Ali          | Dubai        | UAE             |
| ---------- | ------------ | ------------ | --------------- |
| 4          | Jenny        | Berlin       | Germany         |
| ---------- | ------------ | ------------ | --------------- |
| 5          | Lisa         | Berlin       | Germany         |
| ---------- | ------------ | ------------ | --------------- |
| 6          | Priya        | Delhi        | India           |
| ---------- | ------------ | ------------ | --------------- |
| 7          | Wong         | Beijing      | China           |
| ---------- | ------------ | ------------ | --------------- |
| 8          | Julius       | Rome         | Italy           |
| ---------- | ------------ | ------------ | --------------- |
| 9          | Alonso       | Atlanta      | USA             |
| ---------- | ------------ | ------------ | --------------- |
| 10         | Noor         | London       | UK              |
| ---------- | ------------ | ------------ | --------------- |

Filtering rows using regular expreesion in SQL:

-- For MySQL
SELECT * 
FROM students
WHERE student_country REGEXP '[y|d]'

-- For Oracle
SELECT * 
FROM students
WHERE  REGEXP_LIKE (student_country, '(y|d)');

Filtering rows using regular expreesion in Python:

You must have the pandas library installed to run this snippet of code.

import pandas as pd

students[students['student_country'].str.contains(r'y|d',regex=True)]

Filtering rows using regular expreesion in R:

You must have the dplyr and stringr libraries installed to run method 2.

# Method 1
students[grep('(y|d)', students$student_country),]

# Method 2
students %>% filter(str_detect(student_country, '(y|d)'))

Result:

The following output has been taken directly from a Python, SQL or R console using one of the methods demonstrated above. Hence, the way the result is displayed may not look exactly like the one below for all cases always.

  student_id student_name student_city student_country
1          2         Hari       Mumbai           India
2          4        Jenny       Berlin         Germany
3          5         Lisa       Berlin         Germany
4          6        Priya        Delhi           India
5          8       Julius         Rome           Italy