SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 UPPER Case to Proper Case
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

guessme72
Starting Member

33 Posts

Posted - 03/30/2005 :  08:44:03  Show Profile
I have a simple problem.
I have to copy all the rows from one database table to an other database table.
In the table there is a column 'DESCRIPTION'. In the first Table is is all UPPER CASE.
But in my second table I want the column values in 'PROPER CASE'.

How can I do it in SQL SERVER ??

TIA

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 03/30/2005 :  08:50:13  Show Profile
What is 'PROPER CASE'?

CODO ERGO SUM
Go to Top of Page

guessme72
Starting Member

33 Posts

Posted - 03/30/2005 :  09:15:41  Show Profile
quote:
Originally posted by Michael Valentine Jones

What is 'PROPER CASE'?

CODO ERGO SUM



IF the Desc has a value "WHAT IS YOUR NAME?"

I want to save it as "What is your name?"
Proper case or Sentence case..
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 03/30/2005 :  10:07:35  Show Profile  Visit mr_mist's Homepage
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. :)
Go to Top of Page

guessme72
Starting Member

33 Posts

Posted - 03/30/2005 :  10:39:43  Show Profile
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. :)



More than 500 K rows for sure and it will def. increase.
Is there any function in SQL Server?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 03/30/2005 :  10:58:02  Show Profile

-- 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
Go to Top of Page

guessme72
Starting Member

33 Posts

Posted - 03/30/2005 :  13:10:03  Show Profile
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

Actully I was wrong I want it in Title Case.
My column is for POSITION/Title.
So I need them in Title Case
Example: "I Want Them In Title Case"
Sorry for previous mistake.:(



Go to Top of Page

stephe40
Posting Yak Master

218 Posts

Posted - 03/30/2005 :  14:48:09  Show Profile
I suggest doing this on the client side before the data is inserted into the table. SQL just doesn't have the functions to do this eaisly.

- Eric


EDIT:
I originally though it would be better to use regular exprssions, which is why I suggested doing at the presentation layer. Anyway, nevermind that, see what I posed below.

Edited by - stephe40 on 03/30/2005 15:25:18
Go to Top of Page

stephe40
Posting Yak Master

218 Posts

Posted - 03/30/2005 :  15:16:49  Show Profile
Try this:


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')


- Eric

Edited by - stephe40 on 03/30/2005 15:34:21
Go to Top of Page

stephe40
Posting Yak Master

218 Posts

Posted - 03/30/2005 :  15:23:11  Show Profile
You also may want to check out this thread,

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37760

which I found by doing a simple search of the forum here for "title case". Anyway, good luck.



- Eric
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1537 Posts

Posted - 03/30/2005 :  15:27:34  Show Profile  Visit JimL's Homepage
I think I got this From NR.
Once created just call it for the given column



create FUNCTION 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



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO




Jim
Users <> Logic
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/30/2005 :  15:34:53  Show Profile
You could use a function


 

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')




Brett

8-)
Go to Top of Page

stephe40
Posting Yak Master

218 Posts

Posted - 03/30/2005 :  15:39:15  Show Profile
Right now mine does not work for delimeters other than a space. Although, it would be pretty easy to modify it to work for dashes and commas, as it looks like the other one does.

- Eric
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/30/2005 :  16:20:21  Show Profile  Visit jsmith8858's Homepage
here's another one, works for all punctation:

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



select dbo.ProperCase('this,my friends, is a test.wHat DO you think?i like shaquile o''neal')

- Jeff

Edited by - jsmith8858 on 03/30/2005 16:22:33
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/30/2005 :  16:23:52  Show Profile
Do you think there any effecieny differnece between going theough every byte as compared to using CHARINDEX?



Brett

8-)
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 03/30/2005 :  16:42:20  Show Profile  Visit jsmith8858's Homepage
probably not much of a difference; you'd have to test. i would assume the difference would only be measurable if you ran the function quite a few times (i.e., over 10,000 or so ...)

Of course, you can't compare the efficiency of two algorithms that produce different results, right?

- Jeff
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000