The elusive holy grail of SQL queries

Years ago, I suggested a SQL query that would be tremendously valuable for people in my line of work. The query would overcome the limitations of the software regarding specific ID’s of information. Normally, it’s a matter of joining the right tables to display the information you need. In this particular instance, the data are not stored in the usual table. Instead, the data exist in a giant, unformatted text field. One can see the information in the UI, but a special SQL query would be needed to show the information in a report in a meaningful way.

I have given this requirement to several SQL experts, and none of them have figured it out. Some of them claim they could do it, but it would take too much of their time to do it on the ask. I made an attempt at a rudimentary solution at my last client that worked passably for their database, but then I haven’t thought about this query again until yesterday.

I had a 1-1 with someone on my team who would understand the value of this type of report. I happened to bring it up, and he was fired up about the idea that it would be possible. So, I dusted off the previous code and started to update it for my current client’s database. This query is TOUGH. I have to use several functions that I’m not familiar with, and I have to consider the irregularity of how the specific data I’m looking for show up in this giant text field.

I’m pretty far along today, and I hope tomorrow I will finally crack it. The holy grail will be mine.