Create FUNCTION [dbo].[GetLastName]
(
	@name varchar(100)
)
RETURNS varchar(100)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @retValue varchar(100)

	-- Add the T-SQL statements to compute the return value here
	SELECT @retValue=SUBSTRING(@name,CHARINDEX(' ', @name, 1)+1, 100)

	-- Return the result of the function
	RETURN @retValue

END


Go


Create FUNCTION [polarbear2].[GetTimeOnly] 
(
	
@Date datetime
)
RETURNS VARCHAR(20)
AS
BEGIN

	DECLARE @Result VARCHAR(20)

 SELECT @Result=CONVERT(varchar(2),
          CASE
               WHEN DATEPART([hour], @Date) > 12 THEN CONVERT(varchar(2), (DATEPART([hour], @Date) - 12))
               WHEN DATEPART([hour], @Date) = 0 THEN '12'
               ELSE CONVERT(varchar(2), DATEPART([hour], @Date))
          END
     ) + ':' +
     CONVERT(char(2), SUBSTRING(CONVERT(char(5), @Date, 108), 4, 2)) + ' ' + 
     CONVERT(varchar(2),
          CASE
               WHEN DATEPART([hour], @Date) > 12 THEN 'PM'
               ELSE 'AM'
          END
     ) 
	-- Return the result of the function
	RETURN @result

END



Create FUNCTION [polarbear2].[GetNewsTeaser] 
(
	-- Add the parameters for the function here
	@newstext varchar(max)
)
RETURNS varchar(400)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result varchar(400)

Declare @index int
Set @index=0

If len(@newstext)<180
	Return @NewsText


	--First find the first period (end of sentence)	

		Select @index=charindex('.', @newstext, @index)
		if @index>180 --we have enough, now lets grab it and go
			Begin
					Set @result=substring(@newstext, 1, @index)
			End
		else
			Begin
				Select @index=charindex('.', @newstext, @index+1)
					Set @result=substring(@newstext, 1, @index)
			
			End 
	
	RETURN @Result

END


Create FUNCTION [polarbear].[PROPERCASE]
(
--The string to be converted to proper case
@input varchar(8000)
)
--This function returns the proper case string of varchar type
RETURNS varchar(8000)
AS
BEGIN
	IF @input IS NULL 
	BEGIN
		--Just return NULL if input string is NULL
		RETURN NULL
	END
	
	--Character variable declarations
	DECLARE @output varchar(8000)
	--Integer variable declarations
	DECLARE @ctr int, @len int, @found_at int
	--Constant declarations
	DECLARE @LOWER_CASE_a int, @LOWER_CASE_z int, @Delimiter char(3), @UPPER_CASE_A int, @UPPER_CASE_Z int
	
	--Variable/Constant initializations
	SET @ctr = 1
	SET @len = LEN(@input)
	SET @output = ''
	SET @LOWER_CASE_a = 97
	SET @LOWER_CASE_z = 122
	SET @Delimiter = ' ,-'
	SET @UPPER_CASE_A = 65
	SET @UPPER_CASE_Z = 90
	
	WHILE @ctr <= @len
	BEGIN
		--This loop will take care of reccuring white spaces
		WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) > 0
		BEGIN
			SET @output = @output + SUBSTRING(@input,@ctr,1)
			SET @ctr = @ctr + 1
		END

		IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @LOWER_CASE_a AND @LOWER_CASE_z
		BEGIN
			--Converting the first character to upper case
			SET @output = @output + UPPER(SUBSTRING(@input,@ctr,1))
		END
		ELSE
		BEGIN
			SET @output = @output + SUBSTRING(@input,@ctr,1)
		END
		
		SET @ctr = @ctr + 1

		WHILE CHARINDEX(SUBSTRING(@input,@ctr,1), @Delimiter) = 0 AND (@ctr <= @len)
		BEGIN
			IF ASCII(SUBSTRING(@input,@ctr,1)) BETWEEN @UPPER_CASE_A AND @UPPER_CASE_Z
			BEGIN
				SET @output = @output + LOWER(SUBSTRING(@input,@ctr,1))
			END
			ELSE
			BEGIN
				SET @output = @output + SUBSTRING(@input,@ctr,1)
			END
			SET @ctr = @ctr + 1
		END
		
	END
RETURN @output
END