How to convert from UpperCase to TitleCase
How will i do this, Any function you know, that could do the job?
Thanks.
[225 byte] By [Farhad] at [2007-11-19 11:09:26] 
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.
# 1 Re: How to convert from UpperCase to TitleCase
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!
olivthill at 2007-11-9 13:41:50 >
# 2 Re: How to convert from UpperCase to TitleCase
I am using SQL Server and want to do it using SQL.Farhad at 2007-11-9 13:42:51 >
# 3 Re: How to convert from UpperCase to TitleCase
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;
olivthill at 2007-11-9 13:44:01 >
# 4 Re: How to convert from UpperCase to TitleCase
For Oracle you can use Initcaprsreeni at 2007-11-9 13:44:56 >
# 5 Re: How to convert from UpperCase to TitleCase
but i am using SQL Server. Any function avialable to do that in sql server?Farhad at 2007-11-9 13:45:55 >
# 6 Re: How to convert from UpperCase to TitleCase
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
zombie_man23 at 2007-11-9 13:46:55 >
# 7 Re: How to convert from UpperCase to TitleCase
It worked.Thank you so much.
Farhad at 2007-11-9 13:47:59 >
