?
Solved

TSQL: Replace column with Title Case

Posted on 2009-02-17
4
Medium Priority
?
1,134 Views
Last Modified: 2012-05-06
Hi All,

I have a column of data that i want to replace with the same data but as Title Case.

e.g

THE CAT SAT ON THE MAT
~ is replaced with
The Cat Sat On The Mat


0
Comment
Question by:detox1978
4 Comments
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 400 total points
ID: 23661216
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1200 total points
ID: 23661308
Here you go!
drop function dbo.titlecase
go
create function dbo.titlecase(@input nvarchar(max))
returns nvarchar(max)
as
begin
set @input = lower(@input)
 
declare @values table(position int not null primary key clustered, theNCHar nchar(1))
 
;with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
insert into @Values (position,theNChar)
select n, substring(@input, n,1)
from    Nums
where   n <= datalength(@input)/2 
order by n
 
update V
set theNchar = upper(v.theNChar)
from @values v
  left join @values v2
on V.position = v2.position+1
where v2.theNChar=N' '
  or v2.theNChar is null
 
set @input=null
select @input = isnull(@input,N'') + theNChar 
from @Values
order by position
 
return @input
end
go
 
select dbo.titlecase(N'THE CAT SAT ON THE MAT')

Open in new window

0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 400 total points
ID: 23661463
0
 
LVL 2

Author Comment

by:detox1978
ID: 23662914
Thanks.


I was hoping for something a lot simpler.  Will give them a look when i get a chance.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question