2012年3月7日星期三

First time writing, string manipulation?

I'm hoping someone can help! Im using sql2000, and I am attempting to capitalize every 1st letter of a word in a column.

For Example:
"GOLF IS FUN,BOWLING IS GREAT"

What Id like to get as my results:

"Golf is fun, Bowling is great"

Trying to figure out the syntax to get the character after the comma to have a space then capital "B" Thought I could use a charindex but just cant seem to get it.Hi

Welcome to the forum :D

By coincidence I was tootling around Vyas's site earlier today and noticed his proper case function.
http://vyaskn.tripod.com/code.htm#propercase

I am confess I have not used it in anger. I would not be surprised if an extended sproc might be a little faster. There is one optimisation I would do in the first place - one single SELECT @.var = 'this', @.other_var = 'that' rather than multiple SETs.

Anyhoo - see how you get on.

HTH|||Oops - just noticed you are not after propercase. Check out PATINDEX for your requirement e.g. PATINDEX('%, %', MyCol)|||CREATE Proc sp_Parsing( @.String Varchar(255))
AS
Declare @.Recepient Varchar(100)
Declare @.Comma Int
DEclare @.OutPut varchar(8000)
create Table #Test(Names Varchar(55))

set nocount on

While @.String is not null
Begin
Set @.Comma = Patindex('%,%',@.String)
if @.Comma <> 0
Begin
Select @.recepient = Ltrim(Rtrim(Substring(@.string,1,(@.Comma - 1))))
Select @.String = Ltrim(Rtrim(Substring(@.String,(@.Comma + 1),255)))

Insert Into #Test
Values(@.Recepient)
Continue
End
Else
Begin
Select @.recepient = @.string
Select @.String = null
Insert Into #Test
Values(@.Recepient)
Break
End
End
set @.OutPut = ''
Select @.OutPut = @.OutPut + Upper(substring(Names,1,1)) + Lower(Substring(Names,2, Len(Names))) + ',' from #test
Select substring(@.OutPut,1, len( @.OutPut) -1)
Drop Table #Test
GO

Exec sp_Parsing 'GOLF IS FUN,BOWLING IS GREAT'|||string manipulation is pretty much always better done in compiled code, not sql, as pootle suggests.

what sql is good at is set based operations.

没有评论:

发表评论