How to convert from UpperCase to TitleCase

[Farhad]

I have user names stored in a table in capital letters (like JAMES BOND), and i want to convert them to title case (like James Bond)?

How will i do this, Any function you know, that could do the job?

Thanks.

[olivthill]

Which database do you have?
Which language do you use?

With Access' SQL, data can be formatted with "Format", but it cannot do what you want. You have to write a little function to convert your strings after or before your query.

With Oracle's SQL, you have UPPER and LOWER, but that's not enough for your needs.

PHP has the built-in "ucwords" function which would be handy for you.

$bar = ucwords(strtolower($bar)); // HELLO WORLD! --> Hello World!

[Farhad]

I am using SQL Server and want to do it using SQL.

[olivthill]

With Access, you can do:

SELECT UCase$(Left$([Tb_users].[a_name],1)) & LCase$(Mid$([Tb_users].[a_name],2,InStr(2,[Tb_users].[a_name]," ") - 1))
 & UCase$(mid$([Tb_users].[a_name],InStr(2,[Tb_users].[a_name]," ") + 1, 1))
 & LCase$(right$([Tb_users].[a_name], Len([Tb_users].[a_name]) - InStr(2,[Tb_users].[a_name]," ") - 1)
) AS Expr1
FROM Tb_users;

[rsreeni]

For Oracle you can use Initcap

[Farhad]

but i am using SQL Server. Any function avialable to do that in sql server?

[zombie_man23]

CREATE function INITCAP (@inString varchar(4000) )
/* INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric */
returns varchar(4000)
as
BEGIN
DECLARE @i int, @c char(1),@result varchar(255)
SET @result=LOWER(@inString)
SET @i=2
SET @result=STUFF(@result,1,1,UPPER(SUBSTRING(@inString,1,1)))
WHILE @i<=LEN(@inString)
BEGIN
SET @c=SUBSTRING(@inString,@i,1)
IF (@c=' ') OR (@c=';') OR (@c=':') OR (@c='!') OR (@c='?') OR (@c=',')OR (@c='.')OR (@c='_')
IF @i<LEN(@inString)
BEGIN
SET @i=@i+1
SET @result=STUFF(@result,@i,1,UPPER(SUBSTRING(@inString,@i,1)))
END
SET @i=@i+1
END
RETURN @result
END

[Farhad]

It worked.
Thank you so much.