Wednesday, March 18, 2009

In SQL Server convert data from UpperCase to TitleCase

--Create the following function in the sql server

CREATE function INITCAP (@inString varchar(4000) )
returns varchar(4000)
as
BEGIN
DECLARE @i int, @c char(1),@result varchar(255)
SET @result=LOWER(@inString)
SET @i=2
SET @result=STUFF(@result,1,1,UPPER(SUBSTRING(@inString,1,1)))
WHILE @i<=LEN(@inString)
BEGIN
SET @c=SUBSTRING(@inString,@i,1)
IF (@c=' ') OR (@c=';') OR (@c=':') OR (@c='!') OR (@c='?') OR (@c=',')OR (@c='.')OR (@c='_')
IF @i
BEGIN
SET @i=@i+1
SET @result=STUFF(@result,@i,1,UPPER(SUBSTRING(@inString,@i,1)))
END
SET @i=@i+1
END
RETURN @result
END

--And call it inside the stored procedure or in query as follows:
select top 10 dbo.INITCAP(city) from newleads

/* This function returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric */

Friday, March 13, 2009

How to calculate Average with 2 decimal places in SQL Server

I have following query to find average in SQL Server
select top 10 cast(avg(cast(DATEDIFF(day, createdate, getdate()) as decimal)) as decimal(18,2)) as avgDays from tblTemp
Is there any optimized way to find average upto two decimal places? Thanks.