Labels

Wednesday, May 4, 2011

Convert Titled Case

DECLARE @TEXT AS VARCHAR(200)
DECLARE @LEN INT
DECLARE @I INT
SET @TEXT ='HERE GOES MY STRING. Hello world'
SET @TEXT = LOWER(@TEXT)
SET @LEN=LEN(@TEXT)
SET @I=0
SET @TEXT = REPLACE(@TEXT, SUBSTRING(@TEXT,1,1), UPPER(' ' + SUBSTRING(@TEXT,1,1)))
Select @TEXT
WHILE(@I < @LEN)
BEGIN
      SET @TEXT = REPLACE(@TEXT, ' ' + SUBSTRING(@TEXT,@I+1,1), UPPER(' ' + SUBSTRING(@TEXT,@I+1,1)))
      SET @TEXT = REPLACE(@TEXT, '. ' + SUBSTRING(@TEXT,@I+1,1), UPPER('. ' + SUBSTRING(@TEXT,@I+1,1)))
      SET @I = @I + 1
END
SELECT @TEXT
GO
DECLARE @STRING VARCHAR(1000), @PROPER_STRING VARCHAR(1000)
SET @STRING='HERE GOES MY STRING. Hello world'
SET @PROPER_STRING=LOWER(@STRING)
SET @PROPER_STRING=
             REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
             REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
             REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
             REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                   ' '+@PROPER_STRING,
             ' A',' A'),' B',' B'),' C',' C'),' D',' D'),' E',' E'),' F',' F'),
             ' G',' G'),' H',' H'),' I',' I'),' J',' J'),' K',' K'),' L',' L'),
             ' M',' M'),' N',' N'),' O',' O'),' P',' P'),' Q',' Q'),' R',' R'),
             ' S',' S'),' T',' T'),' U',' U'),' V',' V'),' W',' W'),' X',' X'),
             ' Y',' Y'),' Z',' Z')
SELECT LTRIM(@STRING) AS ORIGINAL_STRING,LTRIM(@PROPER_STRING) AS CAPITALIZE

No comments:

Post a Comment