mixed case

I know this is more of a front-end issue, but I need to know how to do it in
TSQL (sql 2k). I have a field that I need to convert to mixed case. The
field contains values such as "LAW OFFICES OF JOHN DOE", and I need it to
read "Law Offices Of John Doe". I'm sure with some work I could come up
with a string parser that will do this, but I'm wondering if anyone out
there has already come up with this code. If so, I'd appreciate knowing how
you accomplished this task.
Thanks in advance,
AndreI got lots of hits when I googled 'SQL Server proper case'. Here's one:
http://vyaskn.tripod.com/code/propercase.txt
Hope this helps.
Dan Guzman
SQL Server MVP
"Andre" <no@.spam.com> wrote in message
news:uaQ5mvAAGHA.4036@.TK2MSFTNGP10.phx.gbl...
>I know this is more of a front-end issue, but I need to know how to do it
>in TSQL (sql 2k). I have a field that I need to convert to mixed case.
>The field contains values such as "LAW OFFICES OF JOHN DOE", and I need it
>to read "Law Offices Of John Doe". I'm sure with some work I could come up
>with a string parser that will do this, but I'm wondering if anyone out
>there has already come up with this code. If so, I'd appreciate knowing
>how you accomplished this task.
> Thanks in advance,
> Andre
>|||using a numbers table, you can easily get this - here's one way
[note: this only considers spaces to be word separators]
create table numbers (number int primary key)
-- populate the numbers table with numbers from 1 to x, however you'd like
create function ProperCase(@.in varchar(8000)) returns varchar(8000)
as
begin
declare @.out varchar(8000)
select @.out=''
declare @.res table (ltr char(1), number int)
insert @.res
select substring(@.in, number, 1) as ltr, number
from numbers
where number<=len(@.in)
select @.out=@.out+
case when number=1
or number in (select number+1 from @.res where ltr ='') then
upper(ltr) else lower(ltr) end
from @.res order by number
return @.out
end
Andre wrote:
> I know this is more of a front-end issue, but I need to know how to do it
in
> TSQL (sql 2k). I have a field that I need to convert to mixed case. The
> field contains values such as "LAW OFFICES OF JOHN DOE", and I need it to
> read "Law Offices Of John Doe". I'm sure with some work I could come up
> with a string parser that will do this, but I'm wondering if anyone out
> there has already come up with this code. If so, I'd appreciate knowing h
ow
> you accomplished this task.
> Thanks in advance,
> Andre
>|||Hi
If you searched Google you would find multiple ways of doing this including
http://www.aspfaq.com/show.asp?id=2299
John
"Andre" wrote:

> I know this is more of a front-end issue, but I need to know how to do it
in
> TSQL (sql 2k). I have a field that I need to convert to mixed case. The
> field contains values such as "LAW OFFICES OF JOHN DOE", and I need it to
> read "Law Offices Of John Doe". I'm sure with some work I could come up
> with a string parser that will do this, but I'm wondering if anyone out
> there has already come up with this code. If so, I'd appreciate knowing h
ow
> you accomplished this task.
> Thanks in advance,
> Andre
>
>|||http://vyaskn.tripod.com/code.htm#propercase
"Andre" <no@.spam.com> wrote in message
news:uaQ5mvAAGHA.4036@.TK2MSFTNGP10.phx.gbl...
>I know this is more of a front-end issue, but I need to know how to do it
>in TSQL (sql 2k). I have a field that I need to convert to mixed case.
>The field contains values such as "LAW OFFICES OF JOHN DOE", and I need it
>to read "Law Offices Of John Doe". I'm sure with some work I could come up
>with a string parser that will do this, but I'm wondering if anyone out
>there has already come up with this code. If so, I'd appreciate knowing
>how you accomplished this task.
> Thanks in advance,
> Andre
>|||Cool, thanks. I did search Google but for mixed case, not proper case.
Thanks for all the tips/links.
Andre