December 9, 2011 at 2:40 pm
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
December 9, 2011 at 2:45 pm
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
December 9, 2011 at 2:46 pm
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.
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
December 9, 2011 at 3:15 pm
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
December 15, 2011 at 4:54 am
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')
December 15, 2011 at 5:51 am
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
Change is inevitable... Change for the better is not.
December 15, 2011 at 6:05 am
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
Change is inevitable... Change for the better is not.
December 15, 2011 at 6:05 am
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.
December 15, 2011 at 6:13 am
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
Change is inevitable... Change for the better is not.
December 15, 2011 at 6:18 am
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
December 15, 2011 at 6:22 am
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...
December 15, 2011 at 6:29 am
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
March 21, 2013 at 12:59 am
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply