Using Substring to return part of text in sql

Question

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.

as an alternative, you can view all objects that depend on other objects quite simply using this query: SELECT * FROM sys.sql_expression_dependencies WHERE referencing_id = OBJECT_ID(N'Production.vProductAndDescription'); — cha
– cha, Commented Nov 26, 2013 at 4:44
Sorry here 'Production.vProductAndDescription' stand for what ? — Nuru Salihu
– Nuru Salihu, Commented Nov 26, 2013 at 5:17

Upendra Chaudhari · Accepted Answer · 2013-11-26 08:32:34Z

1

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.

edited Nov 26, 2013 at 8:32

answered Nov 26, 2013 at 7:25

Upendra Chaudhari

6,5515 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Nuru Salihu Over a year ago

Thank you sir, but it throws "Invalid length parameter passed to the LEFT or SUBSTRING function."

Upendra Chaudhari Over a year ago

In 1st query or 2nd query ? Sorry I missed where condition in 1st query check now if are talking about first query

Upendra Chaudhari Over a year ago

Yes, in some cases there will be no braces ("[" and "]") for table names, so it creates problem, check now my updated 2nd query.

Nuru Salihu Over a year ago

It works now. But it returns fewer result than it suppose to. Also not all the table names surpassed a [dbo]. i think that might be the problem. It returns the table names but mostly without the first alphabet .LOl Nonetheless , i really appreciate your time Upendra. It seems i need a little learning to catch up with your solution also. Thank you sir.

Upendra Chaudhari Over a year ago

Check my updated answer, it will return tables which are in join clause with from.

|

Collectives™ on Stack Overflow

Using Substring to return part of text in sql

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related