I am currently navigating a query table that contains a query_id field and a query_text field. In the query_text field, there are a variety of levels of detail that have been added. For example, a query may be as simple as what’s shown for ID.1, or as complex as ID.2:
--ID.1
Select *
From Custtable;
--ID.2
Select
Custtable.CustId
, locsub.StateCode
From Custtable
Inner join (
Select
Loctable.CustId
, Loctable.CountryCode
, Loctable.StateCode
From Loctable
Where Loctable.CountryCode = 'US'
) locsub
On Custtable.CustId = Locsub.CustId
Where Locsub.StateCode= 'NY'
And left(Custtable.CustId, 5) = 'FOOD-';
What I need to do is be able to reliably select the table alias and join field in the last where clause of something like ID.2. In this example, I would need to be able to select just Locsub.StateCode, not the = ‘NY’, to get a better sense of the relationships in the dataset. At the moment, I am able to select everything after the ‘WHERE’, but am unable to get only the information right before the ‘=’. My assumption is I need a way of indexing the = right after the where, and selecting before that, but I can't figure out how...
I am currently doing the following:
Select
substring(
query_text,
len(query_text) - charindex('erehw', reverse(query_text))
, charindex('=', reverse(query_text))
)
From mytable
This works, but again, it is giving me all information after the where statement, instead of just the join field. Any tips would be appreciated!