• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

text replacing - finding end of sentence and capitalizing next letter


In a SQL Server text field, I would like to do the following::

If a period '.' is followed by a space ' ' replace the next letter with a capital
0
esak2000
Asked:
esak2000
1 Solution
 
Aaron TomoskySD-WAN SimplifiedCommented:
0
 
sammySeltzerCommented:
0
 
baretreeCommented:
do you really need to do that on SQL?
string manipulation functions in SQL are so time wasting, not worth handling them there most of the time but if you need to do so, i guess (though both suggestions work) you'd go by the first suggestion in my opinion
when i ask if you really need to do that in SQL i mean, if you're not gonna update tables but just show the result to a client or something, just remember you're gonna execute the function every single time and SQL string manipulation isn't that good for performance...
0
 
Scott PletcherSenior DBACommented:
Naturally change the varchar(8000) to whatever length you need.
CREATE FUNCTION dbo.CapitalizeAfterPeriodSpace (
    @string varchar(8000)
    )
RETURNS varchar(8000)
AS
BEGIN
DECLARE @byte int
SET @byte = CHARINDEX('. ', @string, 1)
WHILE @byte > 0
BEGIN
    SET @string = STUFF(@string, @byte + 2, 1, UPPER(SUBSTRING(@string, @byte + 2, 1)))
    SET @byte = @byte + 2
    SET @byte = CHARINDEX('. ', @string, @byte)
END --WHILE
RETURN @string
END --FUNCTION

Open in new window

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.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now