Solved

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

Posted on 2004-09-15
7
387 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
insert into table 8 22
SQL Server 2012 r2 - Sum totals 2 23
Return 0 on SQL count 24 28
T-SQL: New to using transactions 9 20
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

785 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