How can i use regex to extract a substring from a column containing file paths?

Question & Answer
, , ,
1

Hi all

I have a column in my dataset that contains file paths. I need to extract a folder name from these, the issue is that the index of these folder names could change (either the second or third item when split by ‘/’).

I have a regex pattern that can extract this but im struggling to implement it. The datasource is an RDS running sql server so i cannot use it at that point.

Grateful for any ideas please.

2

Hello @tomDG, there isn’t a way to implement the regex within QuickSight to dynamically sort through the folder names.

After doing some digging, I definitely see why you are having issues trying to complete this from the RDS SQL server database. I was able to find some possible work-arounds that might allow for some more functionality than LIKE or PATINDEX.

One alternative option that might work would be to utilize a Common Language Runtime function so you can build the function with .NET/C#.

Here is some documentation on integrating that with RDS:

Some other third party options that you might be able to utilize to accomplish this are as followed:

  1. SQL Server Regular Expressions by SQL# (SQLSharp)
  2. T-SQL Regular Expression Functions by Oleg Shilo
  3. SQL Server CLR Library by XSharper

I apologize that there isn’t an easier solution, but let me know if any of these options provided help lead you to your expected output.

3

thanks for the reply. I managed to find a solution to this using PATINDEX and CHARINDEX.

regards
Tom

1 Like
4

Hello @tomDG, I am glad you found an alternative for your use case. Thank you for following up!