?
Solved

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

Posted on 2004-09-15
7
Medium Priority
?
417 Views
Last Modified: 2012-05-05
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












0
Comment
Question by:kingphat
  • 3
  • 2
  • 2
7 Comments
 
LVL 10

Expert Comment

by:AaronAbend
ID: 12065386
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
 
LVL 10

Expert Comment

by:AaronAbend
ID: 12065401
(obvious error - you only want to capitalize the first character of the remaining string, not the whole thing)
0
 

Author Comment

by:kingphat
ID: 12065546
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:kingphat
ID: 12065581
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
 
LVL 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 12065624
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
 

Author Comment

by:kingphat
ID: 12065781
Eugene!  Wow that worked like a charm!  EXACTLY what I needed to be done!  Thanks!
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 12066434
you are very welcome!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question