[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Title Case SQL Statement

Posted on 2005-05-09
5
Medium Priority
?
1,257 Views
Last Modified: 2011-10-03
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
0
Comment
Question by:jsound
  • 2
  • 2
5 Comments
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 200 total points
ID: 13961950
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
 

Author Comment

by:jsound
ID: 13961965
So I take it there is no built-in support for the conversion?
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 1800 total points
ID: 13961985
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
 

Author Comment

by:jsound
ID: 13962017
That'll do nicely! Thanks, gentleman, especially rafrancisco for the code sample!

JB
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13962047
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question