0

I tried to make a function to turn "this is a sentence" into "This Is A Sentence". Instead

I get "ThisIsASentence" ie I lose spaces when the function returns the string. Here's the function definition:

ALTER FUNCTION [dbo].[fn_Case_Correction] (@String VARCHAR(max))
        returns varchar(max) 
AS
BEGIN

DECLARE @p varchar(max)
DECLARE @q varchar(max)


set @p = @String
set @String = ' '

while len(@p) <> 0

begin

set @q =  LEFT(@p,ISNULL(NULLIF(CHARINDEX(' ', @p) - 1, -1),LEN(@p)))

set @q = stuff(@q, 1, 1, upper(substring(@q, 1, 1)))

set @String = stuff(@String, len(@String) + 1, len(@q) + 1, @q + ' ')

set @p = SUBSTRING(@p,ISNULL(NULLIF(CHARINDEX(' ', @p), 0), LEN(@p)) + 1, LEN(@p))

end


return ltrim(@String)

END

dbo.fn_Case_Correction('this is a sentence') returns ThisIsASentence when it should return This Is A Sentence. Later I would like to add a way to ignore prepositions so I would only get This is a Sentence.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Knightwisp
  • 315
  • 1
  • 13
  • Check out this post [Capitalise function](http://stackoverflow.com/questions/55054/whats-the-best-way-to-capitalise-the-first-letter-of-each-word-in-a-string-in-s) – Java Devil Jun 19 '13 at 06:43
  • Thanks, but I just added space(1) to @q and it works. – Knightwisp Jun 19 '13 at 07:17
  • possible duplicate of [Change lower case to upper (title) case using sql query](http://stackoverflow.com/questions/16458112/change-lower-case-to-upper-title-case-using-sql-query) – mwigdahl Jun 24 '14 at 17:46

0 Answers0