SQL Server find a particular string that follows a particular string

Question

Let me try to make this as easy to understand as possible. I have a table with a column that is called 'Script_Text'. This column contains a script. Like, a really long script with class name, function name, the whole drill. I am trying to find the function names on all these scripts. I don't want to go through every one of them individually.

This is NOT what I am looking for:

select * from table
where script_text like '%function%'

Script or query, doesn't matter. Want it for SQL Server. My database has not more than 200 tables. Don't care about efficiency. Thanks

EDIT 1: My assumption on what the query would look like might throw you off, just ignore it. I might be plain stupid on how I think this would work. Also, all functions start with Function keyword. For instance: Function GetUserName(). I want my query to return GetUserName().

EDIT 2: I know this is confusing to follow. It is confusing to articulate it in words in MY head. Let me give more examples.

One of the tables I am working with is ObjectSymbol. This table has 3 columns. SymbolName, Description, Script_Text. Contents of the Script_Text column for one particular symbolname is this

Function PostSearch() As Long
    ' Delete any @DFLT or @T records from the search results
    DO something
    ' Do more things
End Function

A successful query would return PostSearch() for that particular symbolname.

EDIT 3:

imports System    
imports System.Diagnostics    
Imports System.XML    
Imports System.Data    
Imports Microsoft.VisualBasic

Class HookScript    
 Inherits FcProcFuncSetEvent

Function presave() as long
    Do stuff here
End Function

Function IngrPostRowUpdate() as Long
Do more stuff here
   RETURN 1
End Function

Function postsave() As Long
    Do some more stuff here
End Function

Private Function GetValue(val as Object) as String
    If IsDBNull(val) Then
        GetValue = ""
    Else
        GetValue = CStr(val)
    End If
End Function
End Class

GetValue is what is being returned by the query.

Could you try to give more examples/data? I can't see much difference between what you don't want and what you do want - all you have really asked there is that you don't get a select * — Cato
– Cato, Commented Feb 2, 2017 at 16:34
is there any specific character after 'function' word? always one space? — McNets
– McNets, Commented Feb 2, 2017 at 16:36
@Cato Sorry about that. I have edited the question so it makes a bit more sense. — Crazy Cucumber
– Crazy Cucumber, Commented Feb 2, 2017 at 16:37

Gottfried Lesigang · Accepted Answer · 2017-04-26 16:06:34Z

2

What about this?

First I declare a table variable to mock-up a test scenario.

DECLARE @YourTable TABLE(ID INT IDENTITY,SomeScript NVARCHAR(MAX));
INSERT INTO @YourTable VALUES
 (
 N'Do something here
 blah
 blah
 private function HereIsAFunctionName(){
     Some body
 }
 public function SomeOtherFunction(){blah}
 private function OneMore(){
     Body
 }
 ')
 ,(
 N'Do something here
 blah
 blah
 private function ThisIsAFunction(){
     Some body
 }
 ');

The first part (a CTE) will use a string-split-trick to create XML chunks by replacing the word function (with blanks) by </x><x>. Then you can read the beginning of each chunk up to the opening (. You might read to the closing ) to get the paramter's list.

WITH Casted AS
(
    SELECT ID
          ,CAST(N'<x>' + REPLACE((SELECT SomeScript AS [*] FOR XML PATH('')),N' function ',N'</x><x>') + N'</x>' AS XML) AS SplitMe
    FROM @YourTable
)
SELECT Casted.ID
      ,LEFT(x.value(N'.',N'nvarchar(max)'),CHARINDEX('(',x.value(N'.',N'nvarchar(max)'))-1) AS Part 
FROM Casted
OUTER APPLY Casted.SplitMe.nodes(N'x[position()!=1]') AS A(x)

The result

ID  Part
1   HereIsAFunctionName
1   SomeOtherFunction
1   OneMore
2   ThisIsAFunction

EDIT Same for VB-syntax

WITH Casted AS
(
    SELECT ID
          ,CAST(N'<x>' + REPLACE((SELECT REPLACE(SomeScript,'end function','') AS [*] FOR XML PATH('')),N' function ',N'</x><x>') + N'</x>' AS XML) AS SplitMe
    FROM @YourTable
)
SELECT Casted.ID
      ,LEFT(x.value(N'.',N'nvarchar(max)'),CHARINDEX(')',x.value(N'.',N'nvarchar(max)'))) AS Part 
FROM Casted
OUTER APPLY Casted.SplitMe.nodes(N'x[position()!=1]') AS A(x)

UPDATE

Your problem was caused by line breaks, where I expected blanks...

Try this:

DECLARE @YourTable TABLE(ID INT IDENTITY,SomeScript NVARCHAR(MAX));
INSERT INTO @YourTable VALUES
 (
 N'Do something here
 blah
 blah
 private function HereIsAFunctionName(){
     Some body
 }
 public function SomeOtherFunction(){blah}
 private function OneMore(){
     Body
 }
 ')
 ,(
 N'imports System    
imports System.Diagnostics    
Imports System.XML    
Imports System.Data    
Imports Microsoft.VisualBasic

Class HookScript    
 Inherits FcProcFuncSetEvent

Function presave() as long
    Do stuff here
End Function

Function IngrPostRowUpdate() as Long
Do more stuff here
   RETURN 1
End Function

Function postsave() As Long
    Do some more stuff here
End Function

Private Function GetValue(val as Object) as String
    If IsDBNull(val) Then
        GetValue = ""
    Else
        GetValue = CStr(val)
    End If
End Function
End Class
 ');

--The query

WITH Casted AS
(
    SELECT ID
          ,CAST(N'<x>' + REPLACE((SELECT REPLACE(REPLACE(REPLACE(SomeScript,'end function',''),CHAR(13),' '),CHAR(10),' ') AS [*] FOR XML PATH('')),N' function ',N'</x><x>') + N'</x>' AS XML) AS SplitMe
    FROM @YourTable
)
SELECT Casted.ID
      ,LEFT(x.value(N'.',N'nvarchar(max)'),CHARINDEX(')',x.value(N'.',N'nvarchar(max)'))) AS Part 
FROM Casted
OUTER APPLY Casted.SplitMe.nodes(N'x[position()!=1]') AS A(x);

edited Apr 26, 2017 at 16:06

answered Feb 2, 2017 at 17:05

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

11 Comments

pmbAustin Over a year ago

In the original poster's example, the script has "Function foo().... End Function", which means you have to deal with the case where you have occurrences of the 'Function' keyword NOT followed by a function name. But that final "End Function" could conceivably have a space following it, you never know. A slightly complicating factor, but you should use that script in the original question in your test data here.

Gottfried Lesigang Over a year ago

@pmbAustin OK, VB-syntax... Just replace ALL End Function with something else before you do this trick...

Crazy Cucumber Over a year ago

@pmbAustin True, the end function will definitely cause confusion. Did not even think about that.

Crazy Cucumber Over a year ago

@Shnugo This KINDA works. Here is what happened. The declare part created a table. It has the right entries in it. When I cast and select from CTE, it returns all the entries that is in the temp table. BUT.... The script column is NULL for all the rows EXCEPT 1. There are 4 other functions in that script but it returns only this 1 function out of the 14 rows. Any idea why? I can provide with the script if needed.

Crazy Cucumber Over a year ago

@Shnugo I've never gotten people being overly dramatic about someone answering their question right, but I get it now. THANK YOU!

|

Collectives™ on Stack Overflow

SQL Server find a particular string that follows a particular string

1 Answer 1

EDIT Same for VB-syntax

UPDATE

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT Same for VB-syntax

UPDATE

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related