First letter upper case for each word in sql server.

Question

First letter upper case for each word in sql server.

bhaveshp.dba

SSC Eights!

Points: 834
More actions
December 9, 2011 at 2:40 pm

#264722

hello All,
I want to write down stored procedure or function
that will return or generate first letter in Upper case and
rest of the letter in lower case.
I already have table with Facility with facility name.(I have 50 facilityname like this and I want to update this table)
Example :
Facility Name : ABINGTON HEALTH LANSDALE HOSP
and I want a output
Facilityname : Abington Health Lansdale Hosp
Thanks
Bhavesh

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

search the scripts section for "ProperCase" or "InitCaps" foir other examples, there's some out there that look for things like "O'Brian" and "St.James" or "David-Jones".

here's just one of many based on a Jeff Moden Enhancement concept:

Edit: previous function i posted was not working, swapping it for this one instead.

CREATE FUNCTION ProperCase(@OriginalText VARCHAR(8000))

RETURNS VARCHAR(8000)

BEGIN

DECLARE @CleanedText VARCHAR(8000)

;with

a1 as (select 1 as N union all select 1 union all

select 1 union all select 1 union all

select 1 union all select 1),

a2 as (select 1 as N from a1 as a cross join a1 as b),

a3 as (select 1 as N from a2 as a cross join a2 as b),

a4 as (select 1 as N from a3 as a cross join a2 as b),

Tally as (select top (len(@OriginalText)) row_number() over (order by N) as N from a4)

SELECT @CleanedText = ISNULL(@CleanedText,'') +

--first char is always capitalized?

CASE WHEN Tally.N = 1 THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))

WHEN SUBSTRING(@OriginalText,Tally.N -1,1) = ' ' THEN UPPER(SUBSTRING(@OriginalText,Tally.N,1))

ELSE LOWER(SUBSTRING(@OriginalText,Tally.N,1))

END

FROM Tally WHERE Tally.N <= LEN(@OriginalText)

RETURN @CleanedText

END

GO

select dbo.ProperCase('WHAT THE HECK IS GOIN ON AROUND HERE;')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

It's possible you just aren't familiar with the proper terminology for what you're looking for.

Google: T-SQL Proper Case

You'll get a few hundred hits.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

bhaveshp.dba SSC Eights! Points: 834 More actions · Answer 3

The function is nor returning right result set.

SELECT Common.InitialCap ('ABINGTON HEALTH LANSDALE HOSP')

ouput

ABINGTON HEALTH LANSDALE HOSP.

Please let me know if any change.

Thanks

Bhavesh

praneshram SSChasing Mays Points: 631 More actions · Answer 4

Hi ,

You can try using this function

CREATE function dbo.properCase(@string varchar(8000)) returns varchar(8000) as

begin

set @string = lower(@string)

declare @i int

set @i = ascii('a')

while @i <= ascii('z')

begin

set @string = replace( @string, ' ' + char(@i), ' ' + char(@i-32))

set @i = @i + 1

end

set @string = char(ascii(left(@string, 1))-32) + right(@string, len(@string)-1)

return @string

end

go

grant execute on propercase to public

go

select dbo.properCase('ABINGTON HEALTH LANSDALE HOSP')

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

bhaveshp.dba (12/9/2011)
The function is nor returning right result set.
SELECT Common.InitialCap ('ABINGTON HEALTH LANSDALE HOSP')
ouput
ABINGTON HEALTH LANSDALE HOSP.
Please let me know if any change.
Thanks
Bhavesh

Damn. You're right. I'll have to check it against my personal copy to make sure that nothing was lost during multiple posts.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

I'm not sure how the code Lowell posted got messed up (forum software will sometimes do that to special characters) but that code is missing all of the "^" characters in the code. Here's a copy of my original (with the missing characters) and, yep... I tested it again just to be sure...

CREATE FUNCTION dbo.InitialCap(@String VARCHAR(8000))

/***************************************************************************************************

Purpose:

Capitalize any lower case alpha character which follows any non alpha character or single quote.

Revision History:

Rev 00 - 24 Feb 2010 - George Mastros - Initial concept

http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/sql-server-proper-case-function

Rev 01 - 25 Sep 2010 - Jeff Moden

- Redaction for personal use and added documentation.

- Slight speed enhancement by adding additional COLLATE clauses that shouldn't have mattered

- and the reduction of multiple SET statements to just 2 SELECT statements.

- Add no-cap single-quote by single-quote to the filter.

***************************************************************************************************/

RETURNS VARCHAR(8000)

AS

BEGIN

----------------------------------------------------------------------------------------------------

DECLARE @Position INT

;

--===== Update the first character no matter what and then find the next postion that we

-- need to update. The collation here is essential to making this so simple.

-- A-z is equivalent to the slower A-Z

SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,

@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

;

--===== Do the same thing over and over until we run out of places to capitalize.

-- Note the reason for the speed here is that ONLY places that need capitalization

-- are even considered for @Position using the speed of PATINDEX.

WHILE @Position > 0

SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,

@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)

;

----------------------------------------------------------------------------------------------------

RETURN @String;

END ;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 7

Is there a reason why the second line uses UPPER() instead of LOWER()?

I'm trying to test this just using the code inside the function and it is taking a damn long time to convert one little string. 4 minutes and counting so far.

Edit: Ah. Just saw your post about the caret mark. That makes the code go so much faster.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

@lowell,

Just to prevent accidental use of the bad copy of the code, would you mind editing your post to remove the code? Thanks my friend.

And, to be sure, I realize that you didn't do this. Somewhere, somehow, the forum code whacked the characters.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323504 More actions · Answer 9

i tried switching to LOWER as Brandie identified, and i get a function that never finishes executing...in the meantime, i'll post an older version from my supply of 8 differnet Propercase variations, most of them harvested from this post:

http://www.sqlservercentral.com/Forums/Topic1042310-149-1.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 10

Lowell (12/15/2011)
i tried switching to LOWER as Brandie identified, and i get a function that never finishes executing...

That's what I'm running into. I'm not quite sure why it's doing that.

EDIT: I should note that I just noticed Jeff's code has LOWER on the first bit, where yours had UPPER. All I changed was the second bit (UPPER to LOWER) without noticing the LOWER on the first... And now I'm rambling...

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Lowell SSC Guru Points: 323504 More actions · Answer 11

ok i declare mea culpa, now that i see that my post somehow got it's carats stripped out; that's how i found it in my snippets as well. i fixed my saved copy of it, but have no idea how that happened.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nilesh.umaretiya Newbie Points: 5 More actions · Answer 12

SELECT ' ' + UPPER(LEFT(fname, 1)) + LOWER(SUBSTRING(fname, 2, LEN(fname)))

+ ' ' + UPPER(LEFT(lname, 1)) + LOWER(SUBSTRING(lname, 2, LEN(lname)))

AS 'Full Name'

FROM dbo.mReg_Table

-- By Nilesh Umaretiya

-----------------------

Software Engineer

UIPC, Satellite,

Ahmedabad-15

Gujarat, India