Browse by Tags

• splitting strings

• Looping over routines using sp_foreachroutine

  Of all of the undocumented stored procedures shipped with SQL Server, there are two in particular that I constantly use: sp_MSforeachtable and sp_MSforeachdb . These procedures internally loop over each non-Microsoft shipped (i.e. user-defined) table Read More... Posted Wednesday, July 12, 2006 10:45 PM by Adam Machanic | 5 Comments Filed under: Scripts, T-SQL

• Tokenize UDF

  Yes, another string splitting UDF from a guy who's obvioiusly become obsessed with TSQL string splitting. This time we delve into a mysterious world that I call, "Tokenization." So what is Tokenization? It's a word I made up for this problem. But what Read More... Posted Wednesday, July 12, 2006 10:34 PM by Adam Machanic | 1 Comments Filed under: Scripts, T-SQL

• "Reflect" a TSQL routine

  Ever want to see the text of a stored procedure, function, or trigger -- or manipulate the text in some way? sp_helptext works, sort of. But I really don't like the way it handles large procedures (> 4000 characters). They seem to end up with some Read More... Posted Wednesday, July 12, 2006 10:33 PM by Adam Machanic | 0 Comments Filed under: Scripts, T-SQL

• Script out PKs/UNIQUE constraints and referencing FKs

  In the course of my work, I occasionally need to cluster a primary key that's nonclustered, or go the other way, or make some other modification to a primary key... But it's a hassle! All of the foreign keys need to be dropped, the PK needs to be dropped, Read More... Posted Wednesday, July 12, 2006 10:32 PM by Adam Machanic | 17 Comments Filed under: Scripts, T-SQL

• Pattern-based split string

  "hickymanz" asked in the SQL Server Central forums for a method of counting unique words in a text column. Wayne Lawton recommended using a string split function, which was a good idea, but not quite adequate for the job in my opinion. Typical string Read More... Posted Wednesday, July 12, 2006 10:31 PM by Adam Machanic | 9 Comments Filed under: Scripts, T-SQL

• Validate a URL from SQL Server

  File this one in your folder of things you should probably never use -- but maybe, some day, in an emergency, you'll need this. I see posts requesting this functionality all the time. "How do I validate a URL in SQL Server?" Not just the string, but the Read More... Posted Wednesday, July 12, 2006 10:23 PM by Adam Machanic | 21 Comments Filed under: Scripts, T-SQL

• Counting occurrences of a substring within a string

  I have absolutely no idea why anyone wants to do this, but I keep answering the same question in forums: "How do I count the occurrences of a substring [note: usually comma] within a string?" In an effort to thwart carpal tunnel syndrome, I have created Read More... Posted Wednesday, July 12, 2006 10:22 PM by Adam Machanic | 15 Comments Filed under: Scripts, T-SQL

• Splitting a string of unlimited length

  There are many techniques for splitting a string in T-SQL (in other words, taking a character-delimited string and producing a table of the values), the best of which are encapsulated in Erland Sommarskog's famous article . My favorite of his string splitting Read More... Posted Wednesday, July 12, 2006 10:20 PM by Adam Machanic | 14 Comments Filed under: Scripts, splitting strings, T-SQL

• Pattern-based replacement UDF

  As a personal challenge, I decided to write a UDF that will work just like T-SQL's REPLACE() function, but using patterns as input. The first question: How does REPLACE() handle overlapping patterns? SELECT REPLACE('babab', 'bab', 'c') -------------------------------------------------- Read More... Posted Wednesday, July 12, 2006 10:07 PM by Adam Machanic | 14 Comments Filed under: Scripts, T-SQL