Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Trying to iteratively use PATINDEX commands to comb through a large amount of text Expand / Collapse
Author
Message
Posted Wednesday, August 21, 2013 12:20 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 2,268, Visits: 3,429
CleverSQLUserID (8/21/2013)
ScottPletcher (8/14/2013)
The code uses LIKE to compare the string, so just construct your string accordingly.
<snip>
I'll add code to see the actual part(s) of the text that match if you want.</snip>


Yes, please!
I don't even know where to begin for that...


Here's an example; naturally change the "backup" bytes and the total bytes listed to whatever you prefer:



IF OBJECT_ID('tempdb..#search_strings') IS NOT NULL
DROP TABLE #search_strings
CREATE TABLE #search_strings (
search_id int IDENTITY(1, 1) NOT NULL,
search_string nvarchar(40) NOT NULL
)
INSERT INTO #search_strings
SELECT 'linked_server_name1'
--UNION ALL SELECT 'linked_server_name2'
--UNION ALL ...

SELECT
OBJECT_NAME(object_id) AS Object_Name,
search_string,
--list 100 bytes of the definition, starting 40 bytes before the string itself was found
(SELECT SUBSTRING(definition, derived.string_location - 40, LEN(search_string) + 100) FROM sys.sql_modules WHERE object_id = derived.object_id) AS Object_Text
FROM (
SELECT
sm.object_id,
ss.search_string,
PATINDEX('%' + ss.search_string + '%', sm.definition) AS string_location
FROM sys.sql_modules sm
CROSS JOIN #search_strings AS ss
WHERE
CAST(OBJECTPROPERTYEX(sm.object_id, 'IsProcedure') AS int) = 1 AND
sm.definition LIKE '%' + ss.search_string + '%'
) AS derived
ORDER BY
Object_Name




SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1486847
Posted Wednesday, August 21, 2013 10:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
CleverSQLUserID (8/5/2013)
Sorry for necro'ing an old thread, but I'm trying to do something similar. The difference is that I'm looking for multiple strings (matching a pattern) of varying lengths. I've tried to modify the "dog" query, but am falling short of accomplishing what I want to accomplish. Can anyone help shed light?

FYI, I'm trying to search through stored procs and other objects for instances of linked servers, which follow a standard of "LS-%".

select object_name(s.id) Object, substring(s.text, n, charindex(' ', text, n) - n) as Target
from syscomments s
cross join
tally t
where t.n < (select max(len(text)) from syscomments)
and lower(substring(s.text, case when t.N > len(s.text) then len(s.text) else t.n end, case when t.N > len(s.text) then 5 else len(s.text) - n end)) like 'ls-__'
order by object, target



Heh... I guess I'm necro'ing a necro'd thread but let me ask... are you all set on this?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1487040
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse