CODEWITHSUNDEEP
sql-server
Crazy Cucumber
Crazy Cucumber

Reputation: 479

SQL Server find a particular string that follows a particular string

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.

Upvotes: 1

Views: 144

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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);

Upvotes: 2

Related Questions