exactly picking a string using Substring or any other function

Question

I have been struck while writing a query to pick a string . What I'm posting the query is sample data

Declare @tbl table (tblname varchar(20),Query VARCHAR(MAX))

Insert into @tbl (tblname,Query)  values ('Employee','select EmpId, 
            (Select top 1 Dept_ID from Stg.Dept 
                    where Deptid = Deptid) Dept_ID,
            (Select top 1 Dept_ID from Stg.Sub_dept 
                    where Deptid = D.Deptid) SubDept_ID
            from stg.Employee E
            left join stg.Dept D 
                ON D.EMPID = E.EmpID
              WHERE EMPID = (Select EMPID from stg.dept where Deptid = Deptid)')

Query :

select tblname,SUBSTRING(LTRIM(SUBSTRING(Query, CHARINDEX('FROM', Query)+4, LEN(Query))),
CHARINDEX('.', LTRIM(SUBSTRING(Query, CHARINDEX('FROM', Query)+4, LEN(Query))))+1,
CHARINDEX(' ',
SUBSTRING(LTRIM(SUBSTRING(Query, CHARINDEX('FROM', Query)+4, LEN(Query))),
CHARINDEX('.', LTRIM(SUBSTRING(Query, CHARINDEX('FROM', Query)+4, LEN(Query))))+1, LEN(Query)))-1) from 
@tbl

Result :

tblname   Req_tbl
Employee    Dept

Actually this query is picking the stg.Dept table name from sub query in that query column. What I exactly want is to pick up stg.Employee table which is the main table.

Output :

tblname    Req_tbl
Employee    Employee

Can you please suggest on this

If you need to format like that in the query, it's most likely easier with a UNION ALL instead of a table variable. I would skip the header row if you can, it should greatly simplify this. — Jacob H, Oct 17 '18 at 13:44
This would be much easier with a REGEX in an application layer. — Tab Alleman, Oct 17 '18 at 13:46
@JacobH can you brief it as I have tried in different ways unable to move forward .It will be highly appreciative if you correct me in code side — mohan111, Oct 17 '18 at 13:48
@TabAlleman I have tried using regex also but it haven't fetched me desired results — mohan111, Oct 17 '18 at 13:49
is the value you're trying to find in column Query based on the value in the column tblname? — Tim Mylott, Oct 17 '18 at 14:32
I wouldn't even go with regular expressions here. Parsing sql statements is much more difficult than might seem. [Read this post for example](https://stackoverflow.com/questions/26182859/parsing-a-sql-server-query-without-executing-the-query-against-a-database-connec) — Zohar Peled, Oct 17 '18 at 15:06

score 0 · Answer 1 · answered Oct 18 '18 at 17:02

This is not 100% clear but what you're trying to do appears to parse a query to determine is the object defined by @tbl.tblname. exists in that query with a FROM clause in it. For example - for "Employee" you are looking for "stg.employee" (excluding the schema).

If I understand this correctly, you can do this easily with a splitter such as DelimitedSplit8K and do something like this:

Declare @tbl table (tblname varchar(20),Query VARCHAR(MAX))

Insert into @tbl (tblname,Query)  values ('Employee','select EmpId, 
            (Select top 1 Dept_ID from Stg.Dept 
                    where Deptid = Deptid) Dept_ID,
            (Select top 1 Dept_ID from Stg.Sub_dept 
                    where Deptid = D.Deptid) SubDept_ID
            from stg.Employee E
            left join stg.Dept D 
                ON D.EMPID = E.EmpID
              WHERE EMPID = (Select EMPID from stg.dept where Deptid = Deptid)')

SELECT TOP (1) t.tblname, req_table = t.tblname
FROM    @tbl AS t
CROSS APPLY dbo.delimitedSplit8K(t.Query,CHAR(10)) AS s
WHERE PATINDEX('%[^a-zA-Z]from %'+t.tblname+'%', s.item) > 0;

The problem here, however, is that, depending on how uniform your T-SQL code is - this can get complicated and hairy.

exactly picking a string using Substring or any other function

1 Answers1