Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

First letter upper case for each word in sql server. Expand / Collapse
Author
Message
Posted Friday, December 9, 2011 2:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 11:16 AM
Points: 46, 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



Post #1219712
Posted Friday, December 9, 2011 2:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 12,906, Visits: 31,984
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1219715
Posted Friday, December 9, 2011 2:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:44 AM
Points: 6,236, Visits: 7,380
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
Post #1219716
Posted Friday, December 9, 2011 3:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 11:16 AM
Points: 46, 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
Post #1219725
Posted Thursday, December 15, 2011 4:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 10, 2012 4:07 AM
Points: 17, Visits: 62
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')





Post #1222317
Posted Thursday, December 15, 2011 5:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1222336
Posted Thursday, December 15, 2011 6:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1222341
Posted Thursday, December 15, 2011 6:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 7,194, Visits: 6,338
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1222342
Posted Thursday, December 15, 2011 6:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
@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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1222348
Posted Thursday, December 15, 2011 6:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 12,906, Visits: 31,984
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1222354
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse