INITCAP() IN SQL SERVER - SQL Server Forums

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

INITCAP() IN SQL SERVER

Author

Topic

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-10-01 : 03:12:29

in sql server ..

declare @colName varchar(20)
set @colName = 'oKaY'
select upper(left(@colName, 1)) + substring(LOWER(@colName), 2, len(@colName)) --as @colName

this is fine for only 1 word it provides output like ... Okay

but if we have more than 1 word like ... okay fInE
then output should be like ... Okay Fine

is there any solution

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-10-01 : 04:12:13

You need some split functions described in this thread:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648[/url]

Then you can use it as below:


declare @colName varchar(20)
set @colName = 'oKay fiNe Thank YoU'

select stuff((select ' ' + upper(left(data, 1)) + substring(LOWER(data), 2, len(data)) as [text()] from dbo.split(@colName, ' ')
for xml path('')),1,1,'')

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-10-01 : 04:39:40

thanks harsh

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-10-01 : 04:42:06

this is one time solution.. that i've got......

CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN

DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @OutputString VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 1

WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InputString, @Index - 1, 1)
END

IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
BEGIN
IF @PrevChar != '''' OR UPPER(@Char) != 'S'
SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
END

SET @Index = @Index + 1
END

RETURN @OutputString

END
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-10-01 : 08:19:25

Also refer this post http://beyondrelational.com/modules/2/blogs/70/posts/10901/tsql-initcap-function-convert-a-string-to-proper-case.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources