Archive for SQL Server
Counting occurrence of substring in SQL
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
digg
Finding tables where 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.
