Proper/Title Case Correction on Name Fields

Hi,

Below is a function I found on the web for converting a given field to Proper/Title case. As you'd expect it converts the first letter of each word to uppercase. The problem I have is that we have a number of customers with names like AA Legal Practise and Some Law Firm LLP and rather stupidly I forgot to test the script before running it on the live database.

It has also been run on some address fields and has converted values like 1st and 4th into 1St and 4Th.

Can anyone help me amend the function so I can maybe add a list of exceptions/rules that should rename lowecase or uppercase?

This is quite important!

Many thanks,
Danny


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



LVL 1
LFMSupportAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
your function is helpful for a SuperProperCase function, which could be like this:

CREATE Function SuperProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
  declare @r VARCHAR(8000)
  declare @p INT
  declare @l varchar(8000)

  while @text <> ''
  begin
     set @p = instr(@text, ' ')
     if coalesce(@p,0) = 0
     begin
        set @l = @text
        set @text = ''
     end
     else
     begin
        set @l = left(@text, @p)
        set @text = substring(@text, @p+1, len(@p))
     end
   
     --handle the exceptions
     if (@l = 'AA') or (@l = 'LLP' )
       set @l = UPPER(@l)
     else
     begin
       if (@l like '[0-9]%')
         set @l = LOWER(@l)
       else
       begin
          set @l = dbo.ProperCase(@l)
       end
     end
 
     set @r = @r + @l
  end
 
  return (@r)
end

0
 
LFMSupportAuthor Commented:
Thanks for the prompt response!

Am I right in thinking I just list the uppercase exceptions here:

--handle the exceptions
if (@l = 'AA') or (@l = 'LLP' )
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LFMSupportAuthor Commented:
I've just attempted to run the script you posted but am being told that INSTR isn't a valid SQL function. Any ideas what the SQL equivilent is?

Danny
0
 
LFMSupportAuthor Commented:
Also,  should the line:

if (@l = 'AA') or (@l = 'LLP' )

Not be:

if (UPPER(@l) = 'AA') or (UPPER(@l) = 'LLP' )

Danny
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, should be CHARINDEX instead of INSTR, messed up sql server and oracle
0
 
LFMSupportAuthor Commented:
Cheers
0
 
LFMSupportAuthor Commented:
Sorry to rehash this after accepting the answer...

This is only returning NULL values? Any ideas why?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
working on it, please standby ...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here we go:

alter Function SuperProperCase(@Text as varchar(8000))
returns varchar(8000)
as
begin
  declare @r VARCHAR(8000)
  declare @p INT
  declare @l varchar(8000)

  set @r = ''
  while @text <> ''
  begin
     set @p = charindex(' ',@text)
     if coalesce(@p,0) = 0
     begin
        set @l = @text
        set @text = ''
     end
     else
     begin
        set @l = left(@text, @p)
        set @text = substring(@text, @p+1, len(@text)-@p)
     end
   
     -- set @r = @r + '<' + @l + '>'
     --handle the exceptions
     if (@l = 'AA') or (@l = 'LLP' )
       set @l = rtrim(UPPER(@l))
     else
     begin
       if (@l like '[0-9]%')
         set @l = LOWER(@l)
       else
         set @l = dbo.ProperCase(@l)
     end
 
     set @r = ltrim(@r + ' ') + @l
  end
 
  return (@r)
end


go
select dbo.ProperCase('abc and l LLP sq%'), dbo.SuperProperCase('abc and l LLP sq%'), charindex(' ','abc%')
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.