Solved

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

Posted on 2004-09-15
7
380 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 42

Accepted Solution

by:
EugeneZ earned 500 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 42

Expert Comment

by:EugeneZ
ID: 12066434
you are very welcome!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

919 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now