18

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.

Rudy Herdez
  • 195
  • 1
  • 1
  • 8
  • This sounds like a reason to use regex! Are you processing the data at all after getting it, or do you only want filename.mov returned from the DB? It's easier to do this sort of thing after you get the data back, but I think still possible to do it in SQL. – WOUNDEDStevenJones Sep 03 '15 at 17:38
  • I will be processing the data after getting it. – Rudy Herdez Sep 03 '15 at 18:08
  • How are you processing it when it's returned? PHP? If so you can use a combination of http://php.net/manual/en/function.strrpos.php to find the last '/', and then http://php.net/manual/en/function.substr.php to get just the filename. – WOUNDEDStevenJones Sep 03 '15 at 18:13
  • *one* example is hardly a sufficient description of what strings can look like. You need to be much more specific to get a useful query. – Erwin Brandstetter Sep 03 '15 at 23:05
  • @ Erwin, I added more examples in case someone else has a similar question. Thanks! – Rudy Herdez Sep 04 '15 at 02:38

3 Answers3

55

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Is it possible to store the returned substring values into a variable? Sorry for being a complete noob, but I am studying SQL and want to understand more. – Rudy Herdez Sep 04 '15 at 02:32
  • Depends on the context and use case. This may help: http://stackoverflow.com/a/14261927/939860. Else, please ask a new *question*, comments are not the place. – Erwin Brandstetter Sep 04 '15 at 10:32
  • 1
    Hi Erwin. I just return here because got one upvote. Your first solution is great because I think only one `/` on OP requirement. But when I try your second solution with multiple `/` return everything after the first `/` ... instead of the text after **last one** `/` not sure if that was the intent. You can check difference here. http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/9525 – Juan Carlos Oropeza Jun 27 '16 at 16:27
  • @JuanCarlosOropeza: Thanks for pointing out. The previous 2nd solution did not work for multiple `/` in the string. I replaced it with a solution that actually works. Updated fiddle: http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/9577 – Erwin Brandstetter Jun 28 '16 at 14:49
6

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
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
0

you can use the split_part string function, syntax: split_part(string,delimiter,position)

  • string example: exx = "2022-06-12" Note: can be "@ertl/eitd/record_4" etc

  • delimiter: any character for the above example ("-" or "/")

  • Position: nth position,

  • How it works: the above exx string will be split in x times based on the delimiter e.g position 1- 2022, position 2-06, position 3-12 so the nth position helps choose what you want to return thus based on your example: syntax: slipt_part(random_number/filename.xxx,"/",2) output: filename.xxx

Edward Samuel Nabigwaku
  • 1
  • 1