Solved

# Proper/Title Case Correction on Name Fields

Posted on 2006-06-02
1,235 Views
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

0
Question by:LFMSupport

LVL 142

Accepted Solution

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

LVL 1

Author Comment

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

LVL 142

Expert Comment

yes
0

LVL 1

Author Comment

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

LVL 1

Author Comment

Also,  should the line:

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

Not be:

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

Danny
0

LVL 142

Expert Comment

sorry, should be CHARINDEX instead of INSTR, messed up sql server and oracle
0

LVL 1

Author Comment

Cheers
0

LVL 1

Author Comment

Sorry to rehash this after accepting the answer...

This is only returning NULL values? Any ideas why?
0

LVL 142

Expert Comment

working on it, please standby ...
0

LVL 142

Expert Comment

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

## Featured Post

### Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.