Capitalize first charcater after finding a period (.), exclamation point (!), or question mark (?)

Hello all,

I am working through a database and just combined three columns into one. I successfully did this along with making the all cap responses lower case.  I then upper cased the first character.  The problem I am having is how do I capitalize a character after a period etc..?

Example.  if have a sentence like this...

John has a dog. the dog is small! where is his dog?

How do I make the sentence look like this?

John has a dog. The dog is small! Where is his dog?

I need to account for double spacing between periods etc. as well. (EX The dog is small!  Where is his dog?)

Here is the code I'm working with now!  

CREATE FUNCTION CapitalizePeriod (@capword   Varchar(1024))  
RETURNS Varchar(1024) AS  
BEGIN
      Declare @Counter   int,
              @NextCharCap Int,
            @ResultField Varchar(1024)

      Set @Counter = 0
      Set @ResultField = ''      
      Set @NextCharCap = 1

      WHILE @Counter < Len(@CapWord)
      BEGIN
        SET @counter = @counter + 1
        IF SubString(@Capword,@Counter,1) = '.'
          BEGIN
             SET @Resultfield = @ResultField+LOWER(Substring(@CapWord,@counter,1))
            WHILE @Counter < Len(@CapWord)
              BEGIN
                SET @counter = @counter + 1
                IF SubString(@Capword,@Counter,1) = ''
                  BEGIN
                    SET @Resultfield = @ResultField+LOWER(Substring(@CapWord,@counter,1))
                  END
                ELSE
                  BEGIN
                    BREAK
                  END
              END
            BEGIN
              Set @NextCharCap = 1
              SET @Resultfield = @ResultField+Upper(Substring(@CapWord,@counter,1))
              Set @counter = @counter +1
            End
          END
        IF   @NextCharCap = 1
        BEGIN
          SET @Resultfield = @ResultField+Upper(Substring(@CapWord,@counter,1))
          SET @NextCharCap = 0
        END
      ELSE      
        BEGIN
          SET @Resultfield = @ResultField+LOWER(Substring(@CapWord,@counter,1))
        END
      END

      Return (@ResultField)
END












kingphatAsked:
Who is Participating?
 
Eugene ZConnect With a Mentor Commented:
try /from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro2k/html/sql00i11.asp /
with my small modification:

--select dbo.Capitalize('John has a dog. the dog is small! where iS his dog?')--,1)

create FUNCTION dbo.Capitalize (
  @String          VARCHAR(8000)--,
  --@Capitalize_What VARCHAR(8) = 'string'
    -- string:   Capitalize the first letter
    --           of the string
    -- sentence: Capitalize the first letter
    --           of every sentence.
    --           Delimiters: ./!/?
    -- word:     Capitalize the first letter
    --           of every word.
    --           Delimiters: any characters
    --           other than letters and digits.
)
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @Position   SMALLINT,
          @Char       CHAR(1),
          @First_Char CHAR(1),
          @Word_Start SMALLINT,
          @Capitalize_What VARCHAR(8)
  SET @Capitalize_What = LOWER( @Capitalize_What )
  SET @Word_Start = 0
 --IF @Capitalize_What IN ( 'word', 'sentence' )
    BEGIN
    SET @Position = DATALENGTH( @String )
    WHILE @Position >= 0 BEGIN
      SET @Char = CASE @Position
        WHEN 0 THEN '.'
          ELSE UPPER( SUBSTRING(
            @String, @Position, 1 ) )
         END
      IF @Char BETWEEN 'A' AND 'Z'
        OR @Char BETWEEN '0' and '9' BEGIN
        SET @Word_Start = @Position
        SET @First_Char = UPPER( @Char )
      END
      ELSE BEGIN
        IF  @Char in ( '.', '!', '?' ) BEGIN
          IF @Word_Start > 0
            AND @First_Char BETWEEN 'A' AND 'Z'
            SET @String = STUFF(@String, @Word_Start,
              1, @First_Char )
            SET @Word_Start = 0
          END
        END
        SET @Position = @Position - 1
       END
    END
  --  ELSE
BEGIN -- Capitalize the first character
      SET @Position = 0
      WHILE @Position < DATALENGTH( @String )
      BEGIN
        SET @Position = @Position + 1
        SET @Char = UPPER( SUBSTRING( @String,
          @Position, 1 ) )
        IF @Char BETWEEN 'A' AND 'Z'
          OR @Char BETWEEN '0' AND '9' BEGIN
          SET @String = STUFF( @String,
            @Position, 1, @Char )
          SET @Position = 9999
        END
      END
  END
  RETURN( @String )
END
0
 
AaronAbendCommented:
charindex gives you the position of the period...
charindex('.', 'dog. the') returns 4.

You could use regular expressions to test the next 2 or 3 chars, which would test for the letters of the alphabet and uppercase them (you'd probably need a case statement to make sure they were not spaces), BUT

My recommendation would be to take the rest of the string, assume you will always replace multiple spaces with a single or double space (instead of leaving original spacing. So take the remainder of the string, LEFT trim the spaces off it. Capitalize it, then concatenated it to the first part of the string.  Repeat until all occurances have been done.

The syntax is obviously quite complex. If someone provides the details, give them the points, but at least this answer may get you started...
0
 
AaronAbendCommented:
(obvious error - you only want to capitalize the first character of the remaining string, not the whole thing)
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
kingphatAuthor Commented:
I have removed the extra spacing with Trim

Now I just need to know how to capatilize the first character of the next sentence automatically when SQL finds a period etc...
0
 
kingphatAuthor Commented:
Also to note I have serveral thousand rows with multiple sentences that were lowercased and now need to be in Proper case format :)  That is why I am trying to find a way in SQL to do it automatically.
0
 
kingphatAuthor Commented:
Eugene!  Wow that worked like a charm!  EXACTLY what I needed to be done!  Thanks!
0
 
Eugene ZCommented:
you are very welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.