Archive

Posts Tagged ‘SQL Server’

T-SQL Capitalization

March 10, 2010 Leave a comment

Here is the T-SQL to do this

create function ProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
   declare @Reset bit;
   declare @Ret varchar(8000);
   declare @i int;
   declare @c char(1);

   select @Reset = 1, @i=1, @Ret = '';
   
   while (@i <= len(@Text))
   	select @c= substring(@Text,@i,1),
               @Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
               @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
               @i = @i +1
   return @Ret
end

Blair…

Advertisements
Categories: Development Tags: , ,

How to use Group By correctly

February 10, 2010 1 comment

As you may not be aware I am not an SQL guru but I know enough to get by. A lot of the stuff I do with SQL is actually generated by an ORM most of the time. I know enough to make sure the SQL that is generated is not absolute rubbish and I can fine tune it in the ORM as required.

One thing I want to point out is that having multiple coulns in the group by clause like Name, Age, Address just because you get an error from SQL is not the correct way of doing things.

For a great read though grouping basics see the following great articles.

http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server

http://www.sqlteam.com/article/how-to-use-group-by-with-distinct-aggregates-and-derived-tables

These are great articles everyone who is writing SQL should read.

Blair…

Categories: Development Tags: ,