Sentence Case

[jw2000]

I am trying to convert a string input and into Sentence Case.

eg. roses are red. violets are blue. -> Roses are red. Violets are blue.

 

[SQLDenis]

Take a look at upper and left if these are 2 fields

If it's 1 fields then take a look at substring, upper and patindex

http://sqlservercode.blogspot.com/

 

[jw2000]

I can't get this right ...

SELECT dbo.ProperCase('roses are red. violets are blue.')
returns:
Roses Are Red. Violets Are Blue.


but I want:
Roses are red. Violets are blue.

-------------------------------------------------------

FUNCTION ProperCase(@inputstr as varchar(1000))
RETURNS varchar(1000)
AS
BEGIN
declare @Upper as char(1)
declare @len as int
declare @count as int
declare @flag bit
declare @resultstr varchar(100)

set @len = len(@inputstr)
set @count = 2
set @resultstr = upper(left(@inputstr,1))
while @count <= @len
begin
select @Upper = substring(@inputstr,@count-1,1)
if @Upper = ' '
set @flag = 1
else
set @flag = 0

if @flag = 1
select @resultstr = @resultstr + upper(substring(@inputstr,@count,1))
else
select @resultstr = @resultstr + lower(substring(@inputstr,@count,1))
set @count = @count + 1
end

RETURN (@resultstr)
END

 

[jw2000]

Any suggestions?

 

[gmmastros]

A little googling, and I came up with this...

http://www.sqljunkies.com/Forums/ShowPost.aspx?PostID=896#896

Scroll down to the last post. I haven't used it, so I can't verify the accuracy of the UDF.

Good Luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jw2000]

I have already google'd a bunch of threads and have also tested the code at:

http://www.sqljunkies.com/Forums/ShowPost.aspx?PostID=896#896

but it freezes and doesn't work. Thanks anyways. Any other suggestions?

 

[gmmastros]

I see what you mean. In my spare time, I will take a look at it. Sorry for pointing you in the wrong direction.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Try this. It involves 2 UDF's so I wouldn't expect the performance to be too zippy. As a '1 off' solution, it should be fine, or with small 'paragraphs'.

The first UDF

Alter Function Split(@CommaDelimitedFieldNames Varchar(8000),@SplitChar Char(1))  
Returns @Tbl_FieldNames Table  (FieldName VarChar(100))  As  

Begin 
 Set @CommaDelimitedFieldNames =  @CommaDelimitedFieldNames + @SplitChar

 Declare @Pos1 Int
 Declare @pos2 Int
 
 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@CommaDelimitedFieldNames)
 Begin
  Set @Pos1 = CharIndex(@SplitChar,@CommaDelimitedFieldNames,@Pos1)
  Insert @Tbl_FieldNames Select  Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(100))
  Set @Pos2=@Pos1+1
  Set @Pos1 = @Pos1+1
 End 
 Return
End

The Second UDF.

Alter FUNCTION SentenceCase (@paragraph as varchar(8000)) 
RETURNS varchar(8000) 
AS 
BEGIN 

Declare @Temp Table(Sentence VarChar(8000))

Insert Into @Temp(Sentence)
SELECT Lower(LTrim(FieldName) + '.') from dbo.Split('roses are red. violets are blue.', '.')

Update @Temp
Set		Sentence = UPPER(Left(Sentence, 1)) + Lower(Right(Sentence, Len(Sentence)-1))

Declare @Output VarChar(8000)
Set @Output = ''

Select 	@Output = @Output + ' ' + Sentence
From	@Temp

RETURN (LTrim(@Output))
END

And finally, to use it...

SELECT dbo.SentenceCase('roses are red. violets are blue.')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jw2000]

Alter Function Split(@CommaDelimitedFieldNames Varchar(8000),@SplitChar Char(1))
Returns @Tbl_FieldNames Table (FieldName VarChar(100)) As

Begin
Set @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @SplitChar

Declare @Pos1 Int
Declare @pos2 Int

Set @Pos1=1
Set @Pos2=1

While @Pos1<Len(@CommaDelimitedFieldNames)
Begin
Set @Pos1 = CharIndex(@SplitChar,@CommaDelimitedFieldNames,@Pos1)
Insert @Tbl_FieldNames Select Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(100))
Set @Pos2=@Pos1+1
Set @Pos1 = @Pos1+1
End
Return
End


Server: Msg 208, Level 16, State 6, Procedure Split, Line 20
Invalid object name 'Split'.

 

[jw2000]

My bad ... I'm very tired ... changed to:

Create Function Split(@CommaDelimitedFieldNames Varchar(8000),@SplitChar Char(1))

and code compiles, but SELECT dbo.SentenceCase('i like cartoons. you like cookies.') always returns:

Roses are red. Violets are blue.

 

[gmmastros]

I'm sorry. Here's the one that should work.

Alter FUNCTION SentenceCase (@paragraph as varchar(8000)) 
RETURNS varchar(8000) 
AS 
--Dan Piccirillo (dannypic@comcast.net) 
BEGIN 

Declare @Temp Table(Sentence VarChar(8000))

Insert Into @Temp(Sentence)
SELECT Lower(LTrim(FieldName) + '.') from dbo.Split(@Paragraph, '.')

Update @Temp
Set		Sentence = UPPER(Left(Sentence, 1)) + Lower(Right(Sentence, Len(Sentence)-1))

Declare @Output VarChar(8000)
Set @Output = ''

Select 	@Output = @Output + ' ' + Sentence
From	@Temp

RETURN (LTrim(@Output))
END

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jw2000]

Great! How can I capitalize a letter, not only after a period, but after a "/" or "-" ?

Eg.

SELECT dbo.ProperCase('roses are red / violets are blue.')
returns:
Roses are red / Violets are blue.

SELECT dbo.ProperCase('roses are red/violets are blue.')
returns:
Roses are red / Violets are blue.
(note space added before and after "/")

SELECT dbo.ProperCase('roses are red - violets are blue.')
returns:
Roses are red - Violets are blue.

SELECT dbo.ProperCase('roses are red-violets are blue.')
returns:
Roses are red - Violets are blue.
(note space added before and after "-")

 

[gmmastros]

If you study the SentenceCase UDF that I wrote for you, and you understand what is going on, then you shouldn't have too much trouble making it accomodate the '-' and the '/'.

Give it a shot. If you are still struggling with it, after a reasonable amount of time, post your code, and someone should be able to help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jw2000]

Ok will do. Thank you George!

 

[diesirae]

Try this

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

--|
--| Funcion String
--|
ALTER FUNCTION xf_Proper ( @Str VARCHAR(8000) ) RETURNS VARCHAR(8000) 
--------|
	AS
--------|
BEGIN
--------|
	DECLARE @iParcial 	INTEGER
	DECLARE @LenStr         INTEGER
	DECLARE @zReturn        VARCHAR(8000)
	DECLARE @bFlag		BIT
	--|
	SET @LenStr         = Len(@Str)
	SET @iParcial       = 1
	SET @zReturn        = ''
	SET @bFlag	    = 1
	--|
	WHILE @iParcial < @LenStr BEGIN
	--------|
		IF (ASCII(SUBSTRING(@Str,@iParcial,1)) BETWEEN 65 AND 90)  --| From A to Z
		OR (ASCII(SUBSTRING(@Str,@iParcial,1)) BETWEEN 97 AND 122) --| From a to z
		OR (SUBSTRING(@Str,@iParcial,1) IN ('*','{OtherChar}','{OtherChar}')) --| Special chars
		BEGIN
		--------|
			IF @bFlag = 1 BEGIN
			--------|
				SET @zReturn  = @zReturn + UPPER(SUBSTRING(@Str,@iParcial,1))
				SET @bFlag    = 0
			--------|
			END ELSE BEGIN
			--------|
				SET @zReturn  = @zReturn + SUBSTRING(@Str,@iParcial,1) 
			--------|
			END
		--------|
		END ELSE BEGIN
		--------|
			SET @zReturn  = @zReturn + SUBSTRING(@Str,@iParcial,1) 
			SET @bFlag    = 1
		--------|
		END
		SET @iParcial       = @iParcial + 1
	--------|
	END
	SET @zReturn = LEFT(@zReturn,@LenStr)
	--|
	RETURN ( @zReturn )
--------|
END
--|
--| End Function xf_Proper
--|


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

You can replace {OtherChar} with other specail chars that you want the process to ignore to ser capital letters...

I didnt tested this function too much...

 

[jw2000]

diesirae,

Note capitalized and lowercase letters.


SELECT dbo.xf_Proper('roses are red / violets are blue.')
returns:
Roses Are Red / Violets Are Blue
but should return:
Roses are red / Violets are blue.


SELECT dbo.xf_Proper('roses are red/violets are blue.')
returns:
Roses Are Red/Violets Are Blue
but should return:
Roses are red / Violets are blue.
(note space added before and after "/")


SELECT dbo.xf_Proper('roses are red - violets are blue.')
returns:
Roses Are Red - Violets Are Blue
should return:
Roses are red - Violets are blue.


SELECT dbo.xf_Proper('roses are red-violets are blue.')
returns:
Roses Are Red-Violets Are Blue
should return:
Roses are red - Violets are blue.
(note space added before and after "-")