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.
没有评论:
发表评论