quote:

---

Originally posted by Michael Valentine Jones

What is 'PROPER CASE'?

CODO ERGO SUM

---

quote:

---

Originally posted by mr_mist

How many rows are in the table?

This is not a difficult task to do, but it is a difficult task to do well.

-------
Moo. :)

---

-- Convert the first character to uppercase
-- and the rest to lowercase.
select
	DESCRIPTION =
	-- Assuming that the length of DESCRIPTION is varchar(500)
	convert(varchar(500),
	upper(substring(DESCRIPTION,1,1))+
	lower(substring(DESCRIPTION,2,499)))
from
	MyTable

quote:

---

Originally posted by Michael Valentine Jones

-- Convert the first character to uppercase
-- and the rest to lowercase.
select
	DESCRIPTION =
	-- Assuming that the length of DESCRIPTION is varchar(500)
	convert(varchar(500),
	upper(substring(DESCRIPTION,1,1))+
	lower(substring(DESCRIPTION,2,499)))
from
	MyTable

CODO ERGO SUM

---

create function properCase(@string varchar(8000)) returns varchar(8000) as
begin
		
	set @string = lower(@string)
	
	declare @i int
	set @i = ascii('a')
	
	while @i <= ascii('z')
	begin
	
		set @string = replace( @string, ' ' + char(@i), ' ' + char(@i-32))
		set @i = @i + 1
	end
	
	set @string = char(ascii(left(@string, 1))-32) + right(@string, len(@string)-1)
	
	return @string
end

go

grant execute on propercase to public

go

select dbo.properCase('this iS a teSt of thE emergency System... zzZZZZzzz')

 

CREATE FUNCTION udf_TitleCase (@x varchar(7999))
RETURNS varchar(7999)
AS
  BEGIN

	DECLARE @y int
	SET @y = 1
	
	SELECT @x = UPPER(SUBSTRING(@x,1,1))+LOWER(SUBSTRING(@x,2,LEN(@x)-1))+' '
	
	WHILE @y < LEN(@x)
	  BEGIN
		SELECT @y=CHARINDEX(' ',@x,@y)
		SELECT @x=SUBSTRING(@x,1,@y)+UPPER(SUBSTRING(@x,@y+1,1))+SUBSTRING(@x,@y+2,LEN(@x)-@y+1)	
		SELECT @y=@y+1
	  END
	RETURN @x
END

SELECT dbo.udf_TitleCase('THE QUICK FOX JUMPED OVER THE LAZY DOG')

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