Using a CTE to Split a String Into Rows with Line Numbers

Last year while working on my CTE presentation for a SQL Saturday I added a blog post called “Using a CTE to Split a String Into Rows“, and since that posting I have used it many times.   But as things go in development, eventually there is a need to do something more.

Doing some complex string building to create files lately I cam across the need to use a CTE to split strings into rows but to also include line numbers.  To accomplish that I first started with the same function that I used last year, but renamed it, and added a second output column called LineNumber, here is what it looked like:


CREATE FUNCTION dbo.SplitWithLineNumber (@sep char(1), @s varchar(max))
RETURNS table
AS
RETURN (
 WITH splitter_cte AS (
 SELECT CHARINDEX(@sep, @s) as pos,
        cast(0 as bigint) as lastPos,
        0 as LineNumber
 UNION ALL
 SELECT CHARINDEX(@sep, @s, pos + 1),
        cast(pos as bigint),
        LineNumber + 1 as LineNumber
 FROM splitter_cte
 WHERE pos > 0
 )
 SELECT LineNumber,
        SUBSTRING(@s,
                 lastPos + 1,
                 case when pos = 0 then 2147483647
                      else pos - lastPos -1 end) as chunk
 FROM splitter_cte
 );

The things that have changed since the last post “Using a CTE to Split a String Into Rows“, are the following:

  • I added another column to the output called Line Number.
  • Modified the input to be varchar(max).
  • Adjusted the case statement to accommodate varchar(max).
  • Cast the positions as bigint’s in order to accomodate varchar(max).

So now when you run this on the original input, you get the following:

SELECT *
 FROM dbo.SplitWithLineNumber (' ',
          'the quick brown dog jumped over the lazy fox')
OPTION(MAXRECURSION 0);

CTEtoSplitaStringIntoRowswithLineNumbers

Just a small change, but if you need to lave line numbers on the output, this will do it for you.


SQL Saturday Update.

There are 2 upcoming SQL Saturday events that I have pitched presentations at.  These are Vancouver SQL Saturday 198, and Redmond SQL Saturday 2012.

If they are anything like last year these should be a blast.

One of the new presentations that I have prepared for Redmond is the following:

Getting Started as a Technical Speaker

Have you considered speaking at a SQL Saturday, but maybe don’t know where to begin.

Presenting at SQL Saturday events can be fun and fulfilling, but how do you get started?  What tools will make my presentation easier?   Where do I start with building an abstract?  How do I build my presentation?  What if I don’t fill the full time slot?  How do I build my reputation, or my personal brand?   How do I practice so that I will be ready?  What resources and examples are available to me?   These and many more questions will be answered during the presentation.

Database Health Reports- Beta 4 now available.

Today I released Beta 4 of the Database Health Reports.

It has been 2 months since Beta 3, and several bug fixes and new features have been added.  The main focus on this beta was fixing bugs, increasing stability and a new more solid setup program.

My favorite part of the application is the historic waits advisor, which allows you to track down the queries that are causing the most waits over time.

WaitsAdvisor

Bug Fixes

  • Size missing on Disk Usage panel on the Database Overview page. (Bug #86)
  • Fixed the settings so that they don’t get reset on every version number change. Now store in the My Documents directory. (Bug #81)
  • Cached Plans page was just displaying a blank grid when there was no cache plans found, now it displays a message stating that there were none found. (Bug #82)
  • Fixed bug on the Database Overview page relating to the page writes and page reads queries. (Bug #97)
  • Fixed wording problems around tables with no identity columns. (Bug #93)
  • Fixed a bug when switching between full, log and differential backups causing the grid to shown nothing.
  • Fixed a bug involving databases with a different collation. (Bug #95)
  • Fixed a bug causing the defragmentation report to crash. (Bug #94)
  • Fixed several bugs related to case sensitive collations. (Bug #98, #99, #100, #101, #102)
  • Fixed problem where differential backups were not being reported correctly on the Database Overview Backups Panel.

New Features

  • Added warnings when your database collation doesn’t match the collation of TempDB.
  • Added recovery model display into the Database Overview Backups Panel (User Requested).
  • Added last backup time to the Database Overview Backups Panel (User Requested).
  • Connect to database page now prevents connecting to unsupported SQL 2000 or older databases.
  • Connect to database now validates that the user has the needed permissions.

Setup Program Changes

  • Replaced the entire setup program with a new program.
  • Included ability to run the Database Health Reports at the end of the install. (Bug #1)
  • Release notes now shown at the end of the setup process.
  • Included option to add an icon to the desktop.

Its a free download at http://DatabaseHealth.SteveStedman.com, download and enjoy!

Steve Stedman