Recent Postings

Recent Comments


RSS2 feed


Most Popular Posts

Archive for SQL Server

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

Finding tables where column is used

Wednesday, 10th December 2008 7:09pm
There are times when I need to delete or update a column but to do that I need to know every table where the column is used.

Say to find every table where the column UserID is used in a SQL sever database I would use:

Select SyO.Name from Sysobjects SyO
Inner Join Syscolumns SyC
ON SyO.ID = SyC.ID where SyC.Name = 'UserID'
and SyO.Xtype = 'U'

The Xtype = 'U' condition makes sure you just look at tables and not stored procedures or views etc where the column may also be used.

Another method to use would be:

Select Table_name from
where Column_name='UserID'

This second method gives all the areas where that UserID column is used.

delicious delicious | digg digg

Category: SQL Server | Comments : 3