Recent Postings

Recent Comments


RSS2 feed


Most Popular Posts

Archive for October 2009

Counting occurrence of substring in SQL

Saturday, 17th October 2009 12:44am
One of the things that I do quite a few times is finding how many a certain substring occurs in SQL varchar field.

The substring is mostly a comma in a field which is comma delimited.

Knowing the number of commas in the substring will help count the number of elements because you then just add 1 and this is the example I will show below.

For an example, let me consider a list of months say in the Months field, say January, February, March, August, December and comma separated.

To count how many months in this list I find it easy to use the following query assuming in this case that the months are in the variable @months.

declare @months varchar(100)

declare @countMonths int

set @months = 'January, February, March, August, December'

SELECT @countMonths = (LEN(@months) - LEN(REPLACE(@months,',','')) + 1 )

print @countMonths

In this case @countMonths will be shown as 5.

delicious delicious | digg digg

Category: SQL Server | Comments : 0