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
Posted Wednesday, August 21, 2013 12:20 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:53 PM
Points: 3,131, Visits: 4,838
CleverSQLUserID (8/21/2013)
ScottPletcher (8/14/2013)
The code uses LIKE to compare the string, so just construct your string accordingly.
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'

OBJECT_NAME(object_id) AS Object_Name,
--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
PATINDEX('%' + ss.search_string + '%', sm.definition) AS string_location
FROM sys.sql_modules sm
CROSS JOIN #search_strings AS ss
CAST(OBJECTPROPERTYEX(sm.object_id, 'IsProcedure') AS int) = 1 AND
sm.definition LIKE '%' + ss.search_string + '%'
) AS derived

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

"If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." James Blackburn, in closing argument in the "Fatal Vision" murders trial
Post #1486847
Posted Wednesday, August 21, 2013 10:11 PM



Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 38,553, Visits: 35,560
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( 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

Favorite Quotes:
"Has anyone ever told you that a query you have written runs too fast?" - Dwain Camps - 6 Mar 2014

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