Results 1 to 5 of 5

Thread: Loops

  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: 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'

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    well you could try something like this

    --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

  3. #3
    Join Date
    Aug 2003
    Posts
    328

    Re: well you could try something like this

    Wouldn't it be easier with a procedure? Thanks for your help.

  4. #4
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    ...

    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

  5. #5
    Join Date
    Aug 2003
    Posts
    328

    Re: ...

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •