Converting names to Proper Case

[batteam]

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.

 

[markros]

I believe George posted a function quite recently -I'll search for this.

In the meantime assuming always one space

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.

 

[batteam]

Thanks. This will help.

 

[gmmastros]

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.

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...

Select Name, dbo.ProperCase(Name)
From   YourTable

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

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

 

[gmmastros]

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

 

[markros]

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.

 

[markros]

Found the function, but no comment as how to call it:

-- 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

 

[markros]

And now I see that the problem is irrelevant

http://tek-tips.com/viewthread.cfm?qid=1584161

 

[gmmastros]

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