DEV Community

Cover image for Yet Another PostgreSQL-REGEX Article 🀺
Kaustubh Joshi
Kaustubh Joshi

Posted on

Yet Another PostgreSQL-REGEX Article 🀺

πŸ˜„ Introduction

World famous Oxford English Dictionary defines the word Regular as,

Characterized by evenness, order, or harmony in physical form, structure, or organization; arranged in or constituting a constant or definite pattern.

But when it comes to 'Regular' Expressions, they are the most uneven, disordered, and dissonance structures in coding I have ever seen.
But again, When it comes to data cleaning, extracting data from a huge string or working on simple text parsing, nothing else can compete with the power of Regular Expressions.


πŸ› Relevant(?) XKCD

Image description

PostgreSQL regular expressions are a combination of LIKE notations and POSIX regular expression notations.
This post will provide you all the information you need to understand these notations a better and a simpler way.


πŸ’»Metacharacters and Operators

Match Operators:

  • ~ - String matches regular expression
#Example:
    select 'thomas' ~ 't.*ma';
#Output:
#-- true
Enter fullscreen mode Exit fullscreen mode
  • ~* - String matches regular expression, case-insensitively
#Example:
    select 'thomas' ~* 'T.*ma';
#Output:
#-- true
Enter fullscreen mode Exit fullscreen mode
  • !~ - String does not match regular expression
#Example:
    select 'thomas' !~ 't.*max';
#Output:
#-- true
Enter fullscreen mode Exit fullscreen mode
  • !~* - String does not match regular expression, case-insensitively
#Example:
    select 'thomas' !~* 'T.*ma';
#Output:
#-- false
Enter fullscreen mode Exit fullscreen mode

POSIX Regular Expressions:

  • | - Represents alternation, either of two alternatives provided in regex.
#Example:
    select city from taxdata where state ~ 'CA|KS';
#Output:
#-- List of cities which has either CA or KS as state
Enter fullscreen mode Exit fullscreen mode
  • ^ - Matches begining of the line.
#Example:
    select city from taxdata where city ~ '^B';
#Output:
#-- List of cities which name starts with 'B'
Enter fullscreen mode Exit fullscreen mode
  • $ - Matches end of the line.
#Example:
    select city from taxdata where city ~ 'S$';
#Output:
#-- List of cities which name ends with S
Enter fullscreen mode Exit fullscreen mode
  • . (dot) - Matches any charcter.
#Example:
    select city from taxdata where city ~ '.NN';
#Output:
#-- List of cities which has character 'NN' anywhere in the name
Enter fullscreen mode Exit fullscreen mode
  • * - Denotes repetition of the character zero or more times.
#Example:
    select city from taxdata where city ~ '.*N';
#Output:
#-- List of cities which has zero or more repetition of character 'N'
Enter fullscreen mode Exit fullscreen mode

*? - Denotes repetition of the character zero or more times in non-greedy way.

  • + - Denotes repetition of the character one or more times.
#Example:
    select city from taxdata where city ~ '.+N';
#Output:
#-- List of cities which has one or more repetition of character 'N'
Enter fullscreen mode Exit fullscreen mode

+? - Denotes repetition of the character one or more times in non-greedy way.

  • ? - Denotes repetition of the character zero or one time.
#Example:
    select city from taxdata where city ~ '.?N';
#Output:
#-- List of cities which has zero or one repetition of character 'N'
Enter fullscreen mode Exit fullscreen mode

Working with Item Set:

  • {m} - Denotes repetition of the previous item exactly m times.
#Example:
    select city from taxdata where city ~ 'N{2}';
#Output:
#-- List of cities which has 2 repetition of character 'N'
Enter fullscreen mode Exit fullscreen mode
  • {m,} - Denotes repetition of the previous item m or more times.
#Example:
    select city from taxdata where city ~ 'N{2,}';
#Output:
#-- List of cities which has 2 or more repetition of character 'N'
Enter fullscreen mode Exit fullscreen mode
  • {m,n} - Denotes repetition of the previous item at least m and not more than n times.
#Example:
    select city from taxdata where city ~ 'N{2,5}';
#Output:
#-- List of cities which has 2 to 5 repetition of character 'N'
Enter fullscreen mode Exit fullscreen mode
  • () - Parentheses () can be used to group items into a single logical item.
#Example:
    select distinct city from taxdata where city similar to '%(Long|New)%';
#Output:
#-- List of cities which has 'Long' or 'New' in there name
Enter fullscreen mode Exit fullscreen mode
  • ( - Indicates string extraction is to start
  • ) - Indicates string extraction is to end
#Example:
    select substring(email from '.+@(.*)$') from taxdata;
#Output:
#-- Returns domain name from email
Enter fullscreen mode Exit fullscreen mode
  • [...] - Can be used to match characters in listed set
#Example:
    select substring(email from '[0-9]') from taxdata;
#Output:
#-- Returns emails which has any one of 0-9 digit
Enter fullscreen mode Exit fullscreen mode
  • regexp_matches(input, regex) - Returns a text array of matching substring(s) within the first match of a POSIX regular expression pattern to a string
#Example:
    SELECT regexp_match('foobarbequebaz', '(bar)(beque)');
#Output:
#-- Returns output {bar,beque}
Enter fullscreen mode Exit fullscreen mode

πŸ€” Greedy and Lazy

  • greedy - Greedy quantifiers first tries to repeat the token as many times as possible, and gradually gives up matches as the engine backtracks to find an overall match.
    tl;dr; Keep searching until condition is not satisfied.

  • lazy - Lazy quantifier first repeats the token as few times as required, and gradually expands the match as the engine backtracks through the regex to find an overall match.
    tl;dr; Stop searching once condition is satisfied.


πŸ‘β€πŸ—¨ Conclusion

This article should provide you with a birds-eye-view of different regular expressions and notations you can use in your Postgres queries.
I hope you've found this post helpful and do not hesitate to revisit whenever you are stuck finding the correct way to write your regex.πŸ˜‰

NOTE: I would really encourage you to practice these operators on your own dataset or any real life examples. Remember practice is the key to become the hero who saves the day with REGEX.

HAPPY CODING!!!❀️

Image description


πŸ“š References

Top comments (0)