20 June 2006 10:41
tonyrogerson
Turning stuff into "Camel Case" without loops
My good friend Barry Dorrans IM'd me asking if I had a function to convert text into Camel Case but he found one before I could reply, anyway until 2005 we've had to pretty much resort to loops or cursors - but no longer! We can now take advantage of [TEXT()], PATH('') and FOR XML and do it in a single SELECT statement in a set based fashion!
We do need a sequence table for this but it can be a permanent table instead of the table variable i'm using just to show the action...
First we need our sequence table, again - probably best this being permanent as you don't want to build this each time...
set
nocount on
declare
@seq table (
seq int not null primary key
)
declare
@i int
set @i = 1
while @i <= 50
begin
insert @seq values( @i )
set @i = @i + 1
end
Now we can get on and do the logic, notice its really only a single statement, to break this down I will first demonstrate it using a local variable...
declare
@word varchar(100)
set
@word = 'tony rogerson'
select
camelcase = replace( word, ' ', ' ' )
from (
select case when seq = 1 or substring( @word, seq-1, 1 ) = ' ' then upper( substring( @word, seq, 1 ) )
else substring( @word, seq, 1 )
end as [text()]
from @seq
where seq <= len( @word )
for xml path( '' )
) as t( word )
Thats it! If you have a table you want to CamelCase then we simply do this...
declare
@names table (
word varchar(50) not null
)
insert
@names ( word ) values( 'tony rogerson' )
insert @names ( word ) values( 'barry dorrans' )
insert @names ( word ) values( 'trevor dwyer' )
insert @names ( word ) values( 'simon sabin' )
select
camelcase = replace(
( select case when seq = 1 or substring( n.word, seq-1, 1 ) = ' ' then upper( substring( n.word, seq, 1 ) )
else substring( n.word, seq, 1 )
end as [text()]
from @seq
where seq <= len( n.word )
for xml path( '' )
)
, ' ', ' ' )
from
@names n
So, what are we doing here? Well, we are taking advantage of the new [text()] feature of the FOR XML that allows values concatenation, unfortunetly it turns the single space into a control tag hence I do a replace on   to a single space.
Elegant in its simplicity and will scale extremely well - and of course, no need for a UDF anymore!!
Ok, so after Colin Leversuch-Roberts beating me up over not support '-' and 'von' I've expanded the logic thus...
declare
@names table (
word varchar(50) not null
)
declare
@breaks table (
break_on_character char(1) not null
)
declare
@exclude table (
subtext varchar(20) not null
)
insert
@names ( word ) values( 'colin leversuch-roberts being a pain lol' )
insert @names ( word ) values( 'jim von trapp' )
insert
@breaks ( break_on_character ) values( ' ' )
insert @breaks ( break_on_character ) values( '-' )
insert
@exclude ( subtext ) values( ' von' )
select
CamelCase = replace(
( select case when seq = 1
or ( substring( n.word, seq-1, 1 ) IN ( select break_on_character from @breaks )
and not exists ( select *
from @exclude e
where subtext = substring( n.word, seq-1, len( subtext ) )
) )
then upper( substring( n.word, seq, 1 ) )
else substring( n.word, seq, 1 )
end as [text()]
from @seq
where seq <= len( n.word )
for xml path( '' )
)
, ' ', ' ' )
from
@names n
Make sure you check out my more recent entry on removing the control characters, e.g. & and   from the output instead of relying on lots of REPLACE's -> http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
Filed under: SQL Server