Solved

How do I convert from Uppercase to Title case

Posted on 2012-08-28
526 Views
Hi
I have imported some records but the Towns are all in Uppercase and I need them in titlecase = how do I do this in a SQL UPDATE statement?  i.e NEWTOWN ST BOSWELLS needs to be Newtown St Boswells.

Thanks
0
Question by:Lorna70

LVL 65

Accepted Solution

Here's a function you can use.  afaik there is no single keyword that does this.

_____

``````create function properCase(@string varchar(8000)) returns varchar(8000) as

/*
Convert THIS IS A STRING to This Is A String
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47718
*/

-- TESTING
-- Declare @string varchar(8000) = 'THIS IS A STRING'

begin

set @string = lower(@string)

declare @i int
set @i = ascii('a')

while @i <= ascii('z')
begin

set @string = replace( @string, ' ' + char(@i), ' ' + char(@i-32))
set @i = @i + 1
end

set @string = char(ascii(left(@string, 1))-32) + right(@string, len(@string)-1)

RETURN @string
end
``````
0

LVL 16

Assisted Solution

``````update table set field = ltrim(rtrim(lower(field)))
update table set field = fCapFirst(field)

CREATE FUNCTION dbo.fCapFirst(@input NVARCHAR(4000)) RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @position INT
WHILE IsNull(@position,Len(@input)) > 1
BEGIN
SELECT @input =      Stuff(@input,IsNull(@position,1),1,upper(substring(@input,IsNull(@position,1),1))),
@position = charindex(' ',@input,IsNull(@position,1)) + 1
END
RETURN (@input)
END
``````

Which may do all you want. There's also this but even the author says it's really slow!

hth

Mike
0

LVL 68

Assisted Solution

Below is my preferred style, for names/titles at least.  I've set it to capitalize after any non a-z char, including numbers.  To exclude other chars, just add them to the first [] in the PATINDEX string.

For more diversified strings, such as paragraphs, more sophisticated code is needed.

``````SELECT dbo.TitleCase ('  o''malley is a fine name,i''m sure.  ')
SELECT dbo.TitleCase ('  NEWTOWN ST.BOSWELLS,south square 9oclock')

GO
CREATE FUNCTION dbo.TitleCase (
@string varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @byte int
DECLARE @byte_previous int
SET @string = LOWER(@string)
SET @byte = PATINDEX('%[^ ]%', @string) - 1 -- always uppercase the first non-blank character
WHILE 1 = 1
BEGIN
SET @string = STUFF(@string, @byte + 1, 1, UPPER(SUBSTRING(@string, @byte + 1, 1)))
SET @byte_previous = @byte
SET @byte = PATINDEX('%[^a-z][a-z]%', SUBSTRING(@string, @byte + 1, 8000)) + @byte
IF @byte = @byte_previous
BREAK
END --WHILE
RETURN @string
END --FUNCTION
GO
``````
0

Featured Post

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…