SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


First letter upper case for each word in sql server.


First letter upper case for each word in sql server.

Author
Message
bhaveshp.dba
bhaveshp.dba
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 237
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
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74549 Visits: 40983
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 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
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21397 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
bhaveshp.dba
bhaveshp.dba
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 237
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
praneshram
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 63
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
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221142 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221142 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39074 Visits: 9291
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/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
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221142 Visits: 42002
@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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74549 Visits: 40983
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search