Get string after '/' character

Question

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 1

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

SELECT split_part(source_path, '/', 2) ...

If there can be multiple /, and you want the string after the last one, a simple and fast solution would be to process the string backwards with reverse(), take the first part, and reverse() again:

SELECT reverse(split_part(reverse(source_path), '/', 1)) ...

Or you could use the more versatile (and more expensive) substring() with a regular expression:

SELECT substring(source_path, '[^/]*$') ...

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.

db<>fiddle here
_{Old sqlfiddle}