For all the power of SQL Server, some things a still missing. In Microsoft Word or Notepad++, when you search for a word in a document, you get the count of appearances, too. But in SQL, you can search for a string in a VARCHAR field, but the result only appears once. What I really needed was to count the occurrences of a string within a column. In the example below, we needed to change the legacy SQL 2005 style RASIERROR 50000 syntax into the modern RAISERROR(”,16,1) syntax. The business needed a count of the fixes, by object. Of course, I hit up the web and found 90% of what I needed on SQL Mag: SQL Server Pro: Counting Instances of a Word in a Record

I needed to know a bit more information, including object type and object name.

The results look like this:

NAME TYPE FOUND
Trigger_One TR 1
Proc_001 P 3
Proc_002 P 22
Proc_003 P 16

Which is the count of the legacy RAISERROR statements within create statements.

Rudy

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter