TMBTC Post

PostgreSQL parsing paths


PostgreSQL parsing paths

2013-04-10
I’ve been working on a project dealing with file paths inside our PostgreSQL databases and came across a situation where I wanted to parse out the file name from a path. As with everything code related, there are several ways to accomplish the task at hand. Here are the three easiest methods I’ve came up with.
SELECT '/path/to/my/file.txt' AS str_path
  ,REGEXP_REPLACE('/path/to/my/file.txt', '^[^*?"<>|:]*/','')
  ,ARRAY_TO_STRING(REGEXP_MATCHES('/path/to/my/file.txt','([^/]+)$','g'),'')
  ,SPLIT_PART('/path/to/my/file.txt','/',ARRAY_UPPER(REGEXP_SPLIT_TO_ARRAY('/path/to/my/file.txt','\/'),1));
And now for a breakdown of each method.
REGEXP_REPLACE('/path/to/my/file.txt', '^[^*?"<>|:]*/','')
The first method is the most simple method (and fastest). It uses regex to find and replace everything but the last element in a path with empty space (‘’).
ARRAY_TO_STRING(REGEXP_MATCHES('/path/to/my/file.txt','([^/]+)$','g'),'')
The second method finds the last element (the reverse of the first method). The function REGEXP_MATCHES returns a PostgreSQL array so we use the ARRAY_TO_STRING function to convert the array to a string.
SPLIT_PART('/path/to/my/file.txt','/',ARRAY_UPPER(REGEXP_SPLIT_TO_ARRAY('/path/to/my/file.txt','\/'),1))
This third method is probably overkill but it may be useful if you can’t seem to get a regex expressions to work. The method uses REGEX_SPLIT_TO_ARRAY to split the string into an array. Then using the ARRAY_UPPER function we find out how many elements are in the array. This value is then passed to the SPLIT_PART function , telling it which element we want returned. Keep in mind that with each of the methods above you can always replace the delimiter with your own search criteria to come up with fun and exciting ways to pull out that gem of information from a string!

You might also like

Squeaky Steel Case Jersey Chair... Fixed!

2014-09-11

Our offices recently underwent a major renovation. Part of the change was the arrival of new Steel Case Jersey chairs for our work areas. The chairs themselves are adjustable in several areas and quite comfortable. However, by the time the newness wore off the squeaking started. The slightest movement such as leaning back or forward caused an ear piercing squeak to emit from the underside of the chair. As time progressed, you could hear squeaking throughout the entire floor. At times it was quite comical; however, most of the time it simply annoying. The fix: To stop the squeak find


Read More...

Building a Word Clock

2014-09-05

I was recently asked if I could build a Word Clock. Of course, not wanting to back down from a challenge (and a bit of research) I said "yes". One of the objectives of this build, aside from producing a quality product for our client was to document the build and share with the community. After a bit work, I present to you, the Javelin Word Clock. Check out this video a co-worker put together. I think he did a great job. Javelin's Word Clock Want one? You too can build this same clock by following my Instructables


Read More...