Loops

I want to make a loop that extracts the first letter of each word in a given string, converts it to a capital letter, and then concats the string.
For example I want 'Happy Birthday To You' to end up as 'HBTY'--I used the products table in Northwind as example data
--hope this helps

select productname , CHARINDEX(' ', productname)as f1
into #a
from products

select productname , f1, substring(productname,f1+1,40)as bn1, charindex(' ',substring(productname,f1+1,30))as f2
into #b
from #a

select productname , f1, bn1, f2,
bn2 = case when f2 > 0 then substring(bn1,f2+1,40)else '' end,
f3 = case when f2 > 0 then charindex(' ',substring(bn1,f2+1,40))else '' end
into #c
from #b

select productname , f1, bn1, f2, bn2, f3,
bn3 = case when f3 > 0 then substring(bn2,f3+1,40)else '' end,
f4 = case when f3 > 0 then charindex(' ',substring(bn2,f3+1,40))else '' end
into #d
from #c

select productname , f1, bn1, f2, bn2, f3, bn3, f4,
bn4 = case when f4 > 0 then substring(bn3,f4+1,40)else '' end,
f5 = case when f4 > 0 then charindex(' ',substring(bn3,f4+1,40))else '' end
into #e
from #d

select productname,
left(productname,1)+left(bn1,1)+left(bn2,1)+left(b n3,1)+left(bn4,1) as product_abrev
from #e|||Wouldn't it be easier with a procedure? Thanks for your help.|||that was just off the cuff, never done that before, there might be an easier way but thats all I came up with in the few minutes I looked at it.

I am sure if I spent an hour or so on it I could come up with something better.

Good Luck|||Thanks again.