Rudy Herdez Rudy Herdez - 5 months ago 8
SQL Question

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.

Answer

If your case is that simple: exactly one / in the string, use split_part():

SELECT split_part(source_path, '/', 2) FROM movies_history;

If there can be multiple /, and you want the string after the last one, use substring() with a regular expression:

SELECT substring(source_path, '[^/]*$') FROM movies_history;

Explanation:

[...] .. encloses a list of characters to form a character class.
[^...] .. if the list starts with ^ it's the inversion (all characters not in the list).
* .. quantifier for 0-n times.
$ .. anchor to end of string.

Comments