Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Converting names to Proper Case

Status
Not open for further replies.

batteam

Programmer
Joined
Sep 12, 2003
Messages
374
Location
US
Anyone have a neat, efficient way to convert 'JOHN SMITH' to 'John Smith' in a SQL sp? I understand there is no StrConv function in SQL as there is in VBA.

Thanks for any help you can provide.
 
I believe George posted a function quite recently -I'll search for this.

In the meantime assuming always one space
Code:
update myTable set ProperName = upper(left(replace(parsename(MyName,' ','.',1),1) + lower(substring(replace(parsename(MyName,' ','.',1),2,200) + space(1) + upper(left(replace(parsename(MyName,' ','.',2),1) + lower(substring(replace(parsename(MyName,' ','.',2),2,200)

from the top of my head - not tested.
 
Thanks. This will help.
 
I don't think I would parsename here. Some names are quite long and may have more than 4 parts.

I just did a quick check through my database and I found this. Looking at the code, I'm fairly certain I didn't write this. I probably found it on the internet a long time ago, so I can't speak for its quality or performance. I tested it quickly and it seems to work ok.

Code:
CREATE Function [dbo].[ProperCase](@Text as varchar(8000))
returns varchar(8000)as
begin   
declare @Reset bit;   
declare @Ret varchar(8000);   
declare @i int;   
declare @c char(1);   
select @Reset = 1, @i=1, @Ret = '';      
while (@i <= len(@Text))   	
	select @c= substring(@Text,@i,1),               
			@Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,               
			@Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,               
			@i = @i +1   
return @Ret
end

You would use it like this...

Code:
Select Name, dbo.ProperCase(Name)
From   YourTable

If you are satisfied that it works the way you want it to, then...

Code:
Update YourTableName
Set    Name = dbo.ProperCase(Name)



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
I should also mention that doing this sort of thing for names is likely to be a bit problematic because names can get quite interesting. Like....

McCoy, O'Brien, Jr., and roman Numerals (Pope John Paul II).

It should be noted here that strconv also has problems with some names. But... by writing your own function, you can make it accommodate certain variances and build upon this over time as more and more issues are found.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
You wrote an interesting function recently and I found that I forgot to archive that thread :(

I'll check if I at least have the function.
 
Found the function, but no comment as how to call it:

Code:
-- Test query
Create Function [dbo].[SpaceUpper]
(@Original VarChar(8000))
Returns VarChar(8000) As
Begin  
  While PatIndex('%[^ ][A-Z]%', @Original Collate Latin1_General_Bin) > 0    
          Set @Original = Replace(@Original Collate Latin1_General_Bin, 
          SubString(@Original, 1+PatIndex('%[^ ][A-Z]%', 
          @Original Collate Latin1_General_Bin), 1), ' '+ SubString(@Original, 1+PatIndex('%[^ ][A-Z]%', 
          @Original Collate Latin1_General_Bin), 1))  
  Return LTrim(@Original)
End
 
That function was designed to put a space before every capital letter.

Select dbo.SpaceUpper('ThisIsATest')



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top