• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

How do I convert from Uppercase to Title case

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
Lorna70
Asked:
Lorna70
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

Open in new window

0
 
DcpKingCommented:
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

Open in new window



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

hth

Mike
0
 
Scott PletcherSenior DBACommented:
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

Open in new window

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now