UPPER Case to Proper Case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

UPPER Case to Proper Case

Author

Topic

guessme72
Starting Member

33 Posts

Posted - 2005-03-30 : 08:44:03

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)

7020 Posts

Posted - 2005-03-30 : 08:50:13

What is 'PROPER CASE'?

CODO ERGO SUM

guessme72
Starting Member

33 Posts

Posted - 2005-03-30 : 09:15:41

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..

mr_mist
Grunnio

1870 Posts

Posted - 2005-03-30 : 10:07:35

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. :)

guessme72
Starting Member

33 Posts

Posted - 2005-03-30 : 10:39:43

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?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-30 : 10:58:02

[code]
-- 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
[/code]

CODO ERGO SUM

guessme72
Starting Member

33 Posts

Posted - 2005-03-30 : 13:10:03

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.:(

stephe40
Posting Yak Master

218 Posts

Posted - 2005-03-30 : 14:48:09

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.

stephe40
Posting Yak Master

218 Posts

Posted - 2005-03-30 : 15:16:49

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

stephe40
Posting Yak Master

218 Posts

Posted - 2005-03-30 : 15:23:11

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

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-03-30 : 15:27:34

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

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-30 : 15:34:53

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

stephe40
Posting Yak Master

218 Posts

Posted - 2005-03-30 : 15:39:15

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-30 : 16:20:21

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

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-30 : 16:23:52

Do you think there any effecieny differnece between going theough every byte as compared to using CHARINDEX?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-30 : 16:42:20

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources