'Get string after '/' character

I want to extract the string after the character '/' in a PostgreSQL SELECT query.

The field name is source_path, table name is movies_history.

Data Examples:

Values for source_path:

  • 184738/file1.mov
  • 194839/file2.mov
  • 183940/file3.mxf
  • 118942/file4.mp4

And so forth. All the values for source_path are in this format

  • random_number/filename.xxx

I need to get 'file.xxx' string only.

sqlregexpostgresqlpattern-matching


Solution 1:[1]

You need use substring function

SQL FIDDLE

SELECT substring('1245487/filename.mov' from '%/#"%#"%' for '#');

Explanation:

%/

This mean % some text and then a /

#"%#"

each # is the place holder defined in the last part for '#' and need and aditional "

So you have <placeholder> % <placeholder> and function will return what is found inside both placeholder. In this case is % or the rest of the string after /

FINAL QUERY:

 SELECT substring(source_path from '%/#"%#"%' for '#');
 FROM movies_history

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions