Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    1

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

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Welcome to the forum

    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops - just noticed you are not after propercase. Check out PATINDEX for your requirement e.g.
    Code:
    PATINDEX('%, %', MyCol)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Aug 2006
    Posts
    16

    Use this approch...

    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'
    Last edited by greenindia; 03-07-07 at 02:51.

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •