Pattern matching in the end of a string (postgres)

Question

Let's say I have the following table (called filetable) with file names:

file
something.h
something.cc
somethingelse.js
something.py
something.xkh
something.jpeg

I tried the following query:

select file 
  from filetable
    where file ~ E'\.[cc|h|js|py]';

The query output is:

file
something.h
something.cc
somethingelse.js
something.py
something.xkh
something.jpeg

However, I only need files that finish exactly with .cc , .h , .js , .py. How to improve this query?

mu is too short · Accepted Answer · 2014-09-16 17:42:49Z

7

This regex:

\.[cc|h|js|py]

doesn't do what you think it does. [] is a character class so [cc|h|js|py] matches the characters 'c', 'h', 'j', 'p', 's', 'y', and '|' rather than the four extensions that you want to match. If you want to match those four extensions then you want to use parentheses to group your alternations:

\.(cc|h|js|py)

You're also not anchoring your regex so it will match things 'pancakes.html' when you don't want it to. You can fix that by adding an $ to anchor the pattern to the end of the string:

\.(cc|h|js|py)$

And your string doesn't contain any C-style escape sequences (such as \n) so you don't need the E prefix:

where file ~ '\.(cc|h|js|py)$'

answered Sep 16, 2014 at 17:42

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pattern matching in the end of a string (postgres)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related