Menu
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:
And so forth. All the values for source_path are in this format
I need to get 'file.xxx' string only.
771
To get the substring after a specific character, call the substring() method, passing it the index after the character's index as a parameter. The substring method will return the part of the string after the specified character.
We want the substring after the first occurrence of the separator i.e. For that, first you need to get the index of the separator and then using the substring() method get, the substring after the separator. String separator ="-"; int sepPos = str. indexOf(separator); System.
To cut a string after a specific character, you can use the substring() method, slice() method, or split() method. The slice() and the substring() methods work the same as they extract the string by cutting other parts based on the specific character.
The Java String class trim() method eliminates leading and trailing spaces. The Unicode value of space character is '\u0020'. The trim() method in Java string checks this Unicode value before and after the string, if it exists then the method removes the spaces and returns the omitted string.
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
133
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
