Title Case SQL Statement

Is there a SQL statement to convert upper case characters to title case?

I can do it in a VB program if I need to write one, but thought it may be easier just to run a SQL statement. I just can't find any references as to whether there is support for it in T-SQL.

Thanks,

JB
jsoundAsked:
Who is Participating?
 
rafranciscoCommented:
Try this:

CREATE FUNCTION InitCap (@pInputString varchar(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @vIndex          INT
DECLARE @vChar           CHAR(1)
DECLARE @vOutputString   VARCHAR(255)
SET @vOutputString = LOWER(@pInputString)
SET @vIndex = 2
SET @vOutputString = STUFF(@vOutputString, 1, 1, UPPER(SUBSTRING(@pInputString,1,1)))
WHILE @vIndex <= LEN(@pInputString)
BEGIN
    SET @vChar = SUBSTRING(@pInputString, @vIndex, 1)
    IF @vChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
        IF @vIndex + 1 <= LEN(@pInputString)
        BEGIN
            IF @vChar != '''' OR
               UPPER(SUBSTRING(@pInputString, @vIndex + 1, 1)) != 'S'
                SET @vOutputString=STUFF(@vOutputString, @vIndex + 1, 1, UPPER(SUBSTRING(@pInputString, @vIndex + 1, 1)))
        END
    SET @vIndex = @vIndex + 1
END
RETURN @vOutputString
END

0
 
Brian CroweDatabase AdministratorCommented:
You should be able to write a udf that would do this by parsing a string by a delimiter, presumably a space, and converting the first character of each substring to uppercase
0
 
jsoundAuthor Commented:
So I take it there is no built-in support for the conversion?
0
 
jsoundAuthor Commented:
That'll do nicely! Thanks, gentleman, especially rafrancisco for the code sample!

JB
0
 
Brian CroweDatabase AdministratorCommented:
try this one...

CREATE FUNCTION TitleCase (@input varchar(250))
     RETURNS varchar(250)
AS
BEGIN
SET @input = ' ' + rtrim(ltrim(@input))

DECLARE @index int

SET @index = charindex(' ', @input, 1)

WHILE @index > 0
BEGIN
     SET @input = substring(@input, 1, @index) + upper(substring(@input, @index + 1, 1)) +
          substring(@input, @index + 2, len(@input))
     SET @index = charindex(' ', @input, @index + 1)
END

RETURN ltrim(@input)
END
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.

All Courses

From novice to tech pro — start learning today.