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

SQL Server 2000 Forums

Transact-SQL (2000)

Capitalize

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-25 : 06:48:11

hello,
am wondering if there is another way, but to capitalize my words, i use

select upper(left(l_name,1))+ substring(l_name,2,100) as name

is this ok, or is there another way ?

Afrika

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-25 : 07:11:53

There is no other way that I know of.
Instead of hardcoding 100, use LEN()-1.

rockmoose

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-25 : 07:18:26

thanks rockmoose

quote:
use LEN()-1.

sounds more efficient

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-25 : 08:40:37

quote:
Originally posted by rockmoose

There is no other way that I know of.
Instead of hardcoding 100, use LEN()-1.

rockmoose

there's always another way


Declare @blah varchar(100)

Set @blah = 'capitalize'

Select 
	orig=@blah, 
	mod=stuff(@blah,1,1,upper(left(@blah,1)))

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-25 : 08:42:11

there is a cool solution :)

set nocount on
declare @t table(a char) -- possible to make permanent table
declare @i int
set @i=ascii('a')
while @i<=ascii('z')
begin
insert @t select char(@i)
select @i=@i+1
end

declare @s varchar(999)
select @s = 'i want all my words to start with capital letter'
select @s=substring(replace(' '+@s, ' '+a, ' '+upper(a)),2,8000) from @t
select @s

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-25 : 09:01:50

[code]
Declare @s varchar(100)
select @s = 'i want all my words to start with capital letter'

Declare @i int

Set @s = stuff(@s,1,1,upper(left(@s,1)))
Set @i=0
While @i<len(@s) and patindex('% %',right(@s,len(@s)-@i+1))>0
Begin
Set @s = stuff(@s,patindex('% %',right(@s,len(@s)-@i+1))+@i,1,upper(substring(@s,patindex('% %',right(@s,len(@s)-@i+1))+@i,1)))
Set @i = @i + patindex('% %',right(@s,len(@s)-@i+1))
End

Select @s
[/code]

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-25 : 10:12:11

2 Seventhnight

yes, there are many ways like this, for example

declare @str varchar(100)
declare @result varchar(100)
declare @char char(1)
declare @i int

select @str='i want all my words to start with capital letter'
select @result=''

select @i=charindex(' ',@str)
while @i<>0
begin
select @char=upper(left(@str,1))
select @result=@result+@char+substring(@str,2,@i-1)
select @str=right(@str,len(@str)-@i)
select @i=charindex(' ',@str)
end
select @result+upper(left(@str,1))+substring(@str,2,len(@str)-1)

but I like my first one, because it is not ordinary solution.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-25 : 10:48:54

The nice thing about your orig solution is that you loop at max 26 times...
the bad part is you are only dealing with spaces.

my solution will loop more times, but it can easily be modified to include various type of punctuation

6 or 1/2 dozen

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-25 : 12:09:42

the thing is, that every task should has the best solution, so if the problem is to capitalize words, it shouldn't do something else :) because it affects its ability to do its primary task.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-07-25 : 13:09:07

I was talking about how you define words... what about if someone forgets a space after a comma.. or use a colon with no space.

Better yet, this is a good example (parenthesis).

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-25 : 15:07:54

Amazing, but guess i would stick to the first two

Afrika

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-25 : 15:30:00

The revenge of the Camel

.
Well, the only straightforward way I know of

Nice ones, Corey and Stalker.

>> Amazing
Isn't it!

rockmoose

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-26 : 04:22:20

by first two, i meant

select upper(left(l_name,1))+ substring(l_name,2,len() -1) as name

and seventhnight's suggestion
Declare @blah varchar(100)

Set @blah = 'capitalize'

Select
orig=@blah,
mod=stuff(@blah,1,1,upper(left(@blah,1)))

thanks for the other options.

would also explore it, nothing lost in added knowledge :-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-26 : 05:16:30

select upper(left(l_name,1))+ substring(l_name,2,len(l_name) -1) as name

Yes,
I wouldn't use the other techniques either, unless I needed to uppercase the first letter in every word in the text.

Nice to have.

rockmoose

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-07-26 : 15:05:32

ok thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources