tclogo



HotelsCombined.com

Pages

Archives

Categories


Recent Postings

Recent Comments

Feeds

RSS2 feed

Links

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
information_Schema.columns
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