[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2004-09-15
7
Medium Priority
?
411 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

649 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