1 Year Anniversary Summary

Well, my blog is officially 1 year old! I had originally set a goal of posting 2 walkthroughs a month for one year. Honestly I thought that was kind of lofty, but for the most part I pulled it off. While a few months only had 1 post, I far surpassed my goal by posting 35 times (this being my 36th). Even more amazing to me, people are actually reading it….(or at least arriving at my page 0_o )

Anyway I thought I’d share some stats related to the site. The site is hooked up to Google Analytics for stat tracking, as well as Google Ad Sense. Thus far the site has 9,128 page views and I’ve made a grand total of $5.15! ……So, maybe I shouldn’t quit my day job just yet. Overall though, I’d consider this little experiment a success considering I write about nerdy stuff that nobody other than people who work in IT would ever read. Most of the topics have been pretty basic as it’s just way easier (and more importantly takes less time) to do easy stuff. Hopefully in the coming year I’ll make the time to do a few posts related more to Business Intelligence. Anyway, thanks for stopping by and getting your learn on.

Note in the stats below that it took a good 6 months to get the content built up. It’s pretty safe to say that the amount of hits I’ll get over the next year will be way higher than this years.

clip_image002[4]

 

How to find the 1st, 2nd, 3rd or Nth occurrence of a character in a string with T-SQL

Recently I needed to do some parsing of strings, where I needed to find the 2nd occurrence of a character in order to grab the part of the string I needed. So for example I have the following sample data:

clip_image001[4]

Let’s pretend that for whatever reason….the model number, and some other number (I’m making this up as I go) always precede the description. We’ve been assured that those two numbers will always be there, separated by a space, and not containing any spaces themselves. We want to pull back everything in the description column AFTER the 2nd space so our data looks like this:

clip_image002[4]

OK, so the first thing we need to do is find what position the 2nd space is, in each string. I know that I’ve written similar code in the past, and decided I should just write a UDF that will get me the position of the first, second, third, or whatever occurrence I desire for a given character in a string. The code for the UDF is as follows:

CREATE FUNCTION udf_GetNthCharacterOccurrence

(

@p_StringToSearch       VARCHAR(MAX),

@p_CharToSearchFor      VARCHAR(50),

@p_NthOccurrence        INT

)

RETURNS INT

AS

/*=======================================================

Description: Get's the position of the Nth occurrence of

                   a character in a string.

Change log             

Person                  Date  Description

------------      ----------  -----------------------------

Tavis Lovell      2011.08.29  Created

--Execution Example --(find the 2nd space in an address field)

select

      Description,

      dbo.udf_GetNthCharacterOccurrence(Description, ' ', 2)

from

      BlogExample

=======================================================*/

BEGIN--udf

--declare local variables

DECLARE

@v_MinLoop  INT,

@v_CharPosition   INT

--default local variables

SET @v_MinLoop = 2

SET @v_CharPosition =CHARINDEX(@p_CharToSearchFor, @p_StringToSearch)

--only enter loop if we're looking for position >1

WHILE @v_MinLoop <= @p_NthOccurrence

BEGIN--loop

     

      --Figure out what position the character is in

      SET @v_CharPosition =(SELECT CHARINDEX(@p_CharToSearchFor, @p_StringToSearch, @v_CharPosition+1))

      --increment the loop

      SET @v_MinLoop = @v_MinLoop + 1

END--loop

--return the value

RETURN(@v_CharPosition)

END--udf

Just copy, paste and run the code in your db and then you can then call it to find the occurrence of the character you’re looking for. For example, if we wanted to find the position of the 2nd space in the column named “Description” we could do so like this:

SELECT

      [Description],

dbo.udf_GetNthCharacterOccurrence([Description],'', 2)

FROM

      BlogExample

The result of our query is shown below, and we can see that the 2nd column in our result set is indeed the position of the 2nd space for each row.

clip_image003[4]

Now suppose we wanted to return only the portion of the string that was after the 2nd space for each row. We could easily accomplish this using a combination of our UDF, LEN(), and SUBSTRING() …like so:

SELECT

      SUBSTRING([Description], dbo.udf_GetNthCharacterOccurrence([Description],' ', 2)+1,LEN([Description]))

FROM

      BlogExample

I suppose that wraps it up for this blog entry, hope it comes in handy.