T-SQL - Split the next word after two specific words

Question

Imagine that I have the following String: "Select * From table_1 INNER JOIN table_2 ON table_1.A = table_2.B"

I want to extract the two tables of the statement in order to get: table_1 ; table_2.

Basically I need to get the next word after string From and Join. I'm trying with the following query:

DECLARE @StaffName nvarchar(4000)
SET @StaffName = 'Select * From table_1 INNER JOIN table_2 ON table_1.A = table_2.B'

SELECT SUBSTRING
  (
    @StaffName,  
        CHARINDEX('From', @StaffName), 
    CHARINDEX(' ', SUBSTRING(@StaffName, CHARINDEX(' ', @StaffName), 255))
  ) ,
    SUBSTRING(@StaffName, CHARINDEX('From ', @StaffName) + 5, LEN(@StaffName))
    ,
    SUBSTRING(@StaffName, CHARINDEX('From ', @StaffName) + 5, LEN(@StaffName))
    ,
    RIGHT(@StaffName,LEN(@StaffName)-CHARINDEX('From ',@StaffName))

But I'm not getting what I want :(

Anyone can help me?

Many thanks!

Answer 1

Looking at SQLZim's answer which is PERFECTLY VALID (+1), made me realize that I'm tired of extracting portions of strings. As a consequence, I modified my parse function to accept TWO Multi-Character Delimiters. So, IF you are open to a UDF, consider the following:

On a side note: Being a Table-Valued-Function, it is easy to incorporate into a CROSS APPLY or as a single query

For Your Specific Request, we're looking for the key words of FROM and ON

Declare @String varchar(max) = 'Select * From table_1 INNER JOIN table_2 ON table_1.A = table_2.B'
Select Table1 = left(RetVal,charindex(' ',RetVal+' ')-1)
      ,Table2 = right(RetVal,charindex(' ',reverse(RetVal)+' ')-1)
 From [dbo].[udf-Str-Extract] (@String,' from ',' on ')

Returns

Table1  Table2
table_1 table_2

A Generic Example:

Let's say that we're looking for values between {}'s

Declare @String varchar(max) = 'co-101{12345},co-513{22578}'
Select * From [dbo].[udf-Str-Extract] (@String,'{','}')

Returns

RetSeq  RetPos  RetLen  RetVal
1       8       5       12345
2       22      5       22578

Another Generic Example

Declare @String varchar(max) = '<root><firstmame>John</firstname><lastname>Cappelletti</lastname><phone>(401) 555-1212</phone></root>'
Select * From [dbo].[udf-Str-Extract] (@String,'>','<')

Returns

RetSeq  RetPos  RetLen  RetVal
1       18      4       John
2       44      11      Cappelletti
3       73      14      (401) 555-1212

The UDF if Interested

CREATE FUNCTION [dbo].[udf-Str-Extract] (@String varchar(max),@Delimiter1 varchar(100),@Delimiter2 varchar(100))
Returns Table 
As
Return (  

with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
       cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
       cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
       cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)

Select RetSeq = Row_Number() over (Order By N)
      ,RetPos = N
      ,RetLen = charindex(@Delimiter2,RetVal)-1
      ,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1)
 From (Select A.N,RetVal = ltrim(rtrim(Substring(@String, A.N, A.L))) From cte4 A ) A
 Where charindex(@Delimiter2,RetVal)>1
)
/*
Max Length of String 1MM characters

Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[udf-Str-Extract] (@String,'[[',']]')
*/