make the first letter of a word as capital letter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

make the first letter of a word as capital letter

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2009-04-14 : 05:16:09

hi friends,

i need query to make the first letter of a word as capital.

I am having field name. The names first letter should be caps.
inserting values in the table may be any either caps or small letter. but displaying should be first letter caps.

Thanks in advance,

shekhar
Starting Member

3 Posts

Posted - 2009-04-14 : 05:25:50

declare @test nvarchar(200)
set @test = 'test cases'

select UPPER(substring(@test,1,1)) + SUBSTRING(@test,2,len(@test)-1)

vidhya
Posting Yak Master

108 Posts

Posted - 2009-04-14 : 05:31:20

thanks sekhar

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-14 : 06:19:23

Here is a proper case function for you which do the Propercase tasks to a string...

CREATE FUNCTION dbo.ProperCase
(@string VARCHAR(255))
RETURNS VARCHAR(255) AS
BEGIN

– Declarations
DECLARE @return VARCHAR(255)
DECLARE @len INT
DECLARE @finished BIT
DECLARE @pos INT
DECLARE @thischar CHAR(1)
DECLARE @thisasc INT
DECLARE @capitalise BIT

– Initialise
SELECT @finished = 0
SELECT @string = LOWER(@string)
SELECT @return = @string
SELECT @len = DATALENGTH(@return)

– Failsafe
IF @len = 0
SELECT @finished = 1

– Loop over all characters: capitalise first character and those after spaces, replace underscores with spaces
SELECT @pos = 0
SELECT @capitalise = 1
WHILE @finished = 0
BEGIN

– Next position in string
SELECT @pos = @pos + 1

– Done?
IF @pos > @len
SELECT @finished = 1
ELSE
BEGIN
– Read this character
SELECT @thischar = SUBSTRING(@return, @pos, 1)
SELECT @thisasc = ASCII(@thischar)
IF @thischar IN ('_', ' ')
BEGIN
SELECT @return = LEFT(@return, @pos - 1) + ' ' + RIGHT(@return, @len - @pos)
SELECT @capitalise = 1
END
ELSE
BEGIN
IF @capitalise = 1 AND (@thisasc BETWEEN 97 AND 122)
BEGIN
SELECT @thisasc = @thisasc - 32
SELECT @return = LEFT(@return, @pos - 1) + CHAR(@thisasc) + RIGHT(@return, @len - @pos)
SELECT @capitalise = 0
END
END
END
END

– Done
RETURN @return

END
GO

select dbo.PropCase ('ashish gilhotra wat are you doing')

Thanks May be like that u want

mase2hot
Starting Member

36 Posts

Posted - 2010-04-18 : 10:56:06

Hi,

how do I get the function to run against a colum in my table? I tried

execute dbo.ProperCase (select first_name from dbo.clients)

but get error:

Procedure or function 'PROPERCASE' expects parameter '@input', which was not supplied.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-18 : 13:03:04

select dbo.ProperCase(first_name) from dbo.clients

No, you're never too old to Yak'n'Roll if you're too young to die.

mase2hot
Starting Member

36 Posts

Posted - 2010-04-18 : 13:24:58

thanks for that worked instantly! How do I commit the changes though to the table? Sorry I'm used to using Oracle....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-18 : 13:27:42