SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 make the first letter of a word as capital letter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vidhya
Posting Yak Master

102 Posts

Posted - 04/14/2009 :  05:16:09  Show Profile  Reply with Quote
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 - 04/14/2009 :  05:25:50  Show Profile  Reply with Quote
declare @test nvarchar(200)
set @test = 'test cases'

select UPPER(substring(@test,1,1)) + SUBSTRING(@test,2,len(@test)-1)
Go to Top of Page

vidhya
Posting Yak Master

102 Posts

Posted - 04/14/2009 :  05:31:20  Show Profile  Reply with Quote
thanks sekhar
Go to Top of Page

ashishashish
Constraint Violating Yak Guru

India
408 Posts

Posted - 04/14/2009 :  06:19:23  Show Profile  Send ashishashish a Yahoo! Message  Reply with Quote
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
Go to Top of Page

mase2hot
Starting Member

36 Posts

Posted - 04/18/2010 :  10:56:06  Show Profile  Reply with Quote
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.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 04/18/2010 :  13:03:04  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

mase2hot
Starting Member

36 Posts

Posted - 04/18/2010 :  13:24:58  Show Profile  Reply with Quote
thanks for that worked instantly! How do I commit the changes though to the table? Sorry I'm used to using Oracle....
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 04/18/2010 :  13:27:42  Show Profile  Visit webfred's Homepage  Reply with Quote
You want to update your table?
In your first post I see: but displaying should be first letter caps


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

tkizer
Almighty SQL Goddess

USA
37276 Posts

Posted - 04/18/2010 :  13:30:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
update dbo.clients
set first_name = dbo.ProperCase(first_name)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

mase2hot
Starting Member

36 Posts

Posted - 04/18/2010 :  14:06:47  Show Profile  Reply with Quote
thanks worked a treat!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000