CODEWITHSUNDEEP
sqlsql-server
Nuru Salihu
Nuru Salihu

Reputation: 4928

Using Substring to return part of text in sql

I am trying to return a word that follows the table name after "FROM" in my stored procedure. I am using a substring function like below.

SELECT 
  SUBSTRING(text,CHARINDEX('FROM',text)+8, CHARINDEX('with',text,9)+10) AS [nolock],
  name,
  type
FROM 
   Volare_2005.sys.sysobjects so with (nolock) 
INNER JOIN 
  Volare_2005.sys.syscomments sc with (nolock)
  ON so.id=sc.id where type='p'

I am well aware that a table name surpass any "FROM" and is therefore trying to return whatever comes after the table name. It can be "with" or "INNER " or etc. The above query has some issue because, table name varies in length and I am not sure how to go about this. Also I am new to this substring function, just learning about it. Any help or alternative would be appreciated.

Additional info. I am trying to compare what ever word comes after a table name in a stored procedure text and determine if its "with", then proceed else return the name of the procedure. This is quite challenging to me.

Upvotes: 0

Views: 161

Answers (1)

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

It's very hard to check string for table names using PATINDEX() or CHARINDEX() functions for your requirements, but you can do something like this :

    SELECT  CASE    CHARINDEX(']',RIGHT(text,LEN(text)-25), PATINDEX('%FROM__dbo__%',RIGHT(text,LEN(text)-25)) + 11)
                WHEN 0 THEN SUBSTRING(RIGHT(text,LEN(text)-25), PATINDEX('%FROM__dbo__%',RIGHT(text,LEN(text)-25)) + 10, CHARINDEX(' ',RIGHT(text,LEN(text)-25), PATINDEX('%FROM__dbo__%',RIGHT(text,LEN(text)-25)) + 11)- (PATINDEX('%FROM__dbo__%',RIGHT(text,LEN(text)-25)) + 10))
                ELSE  SUBSTRING(RIGHT(text,LEN(text)-25), PATINDEX('%FROM__dbo__%',RIGHT(text,LEN(text)-25)) + 11, CHARINDEX(']',RIGHT(text,LEN(text)-25), PATINDEX('%FROM__dbo__%',RIGHT(text,LEN(text)-25)) + 11)- (PATINDEX('%FROM__dbo__%',RIGHT(text,LEN(text)-25)) + 10)) END AS [nolock],
        name,
        type, text
FROM sys.sysobjects so with (nolock) INNER JOIN sys.syscomments sc with (nolock) ON so.id=sc.id 
where type='p' 
and PATINDEX('%FROM__dbo__%',text) > 0

UNION

SELECT  CASE    CHARINDEX(']',RIGHT(text,LEN(text)-25), PATINDEX('%JOIN__dbo__%',RIGHT(text,LEN(text)-25)) + 11)
                WHEN 0 THEN SUBSTRING(RIGHT(text,LEN(text)-25), PATINDEX('%JOIN__dbo__%',RIGHT(text,LEN(text)-25)) + 10, CHARINDEX(' ',RIGHT(text,LEN(text)-25), PATINDEX('%JOIN__dbo__%',RIGHT(text,LEN(text)-25)) + 11) - (PATINDEX('%JOIN__dbo__%',RIGHT(text,LEN(text)-25)) + 10))
                ELSE  SUBSTRING(RIGHT(text,LEN(text)-25), PATINDEX('%JOIN__dbo__%',RIGHT(text,LEN(text)-25)) + 11, CHARINDEX(']',RIGHT(text,LEN(text)-25), PATINDEX('%JOIN__dbo__%',RIGHT(text,LEN(text)-25)) + 11) - (PATINDEX('%JOIN__dbo__%',RIGHT(text,LEN(text)-25)) + 10)) END  AS [nolock],
        name,
        type, text
FROM sys.sysobjects so with (nolock) INNER JOIN sys.syscomments sc with (nolock) ON so.id=sc.id 
where type='p' 
and PATINDEX('%JOIN__dbo__%',text) > 0

NOTE : For some cases there will be blank space at left side in text field which you need to trim to work it properly. Also in some cases there will be no braces ("[" and "]") for table names, so you should change it accordingly.

Upvotes: 1

Related Questions