taking strings apart

Results 1 to 3 of 3

Thread: taking strings apart

  1. #1
    Join Date
    Dec 1969

    Default taking strings apart

    Hello,<BR> what i am trying to do is take apart names and then sort them alphabetically and when a new letter starts i need to display A, B, C and so on..all one one page..here is an example<BR>Lets say my DB has the following Names(these names are not broken up, but are stroed in one field called NAME)<BR>-----<BR>Joe Shmoe<BR>John Carlo<BR>Some Guy<BR>Jim World<BR>Maria Hello<BR>----<BR>Now the query should take these names and process them and then alphabatize them as such..<BR>-----<BR>C<BR>John Carlo<BR>G<BR>Some Guy<BR>H<BR>Maria Hello<BR>S<BR>Joe Shmoe<BR>W<BR>Jim World<BR>---------<BR>and ofcourse if there are multiple names under a letter then they should be alphabatized...<BR>Here is what i have so far<BR>------<BR>SELECT DISTINCT author, RIGHT(author, LEN(author) - CHARINDEX(&#039; &#039;, author)) AS lastname <BR>FROM paceprints.stocklist <BR>WHERE contemp=1 and canpurchase = 1 <BR>ORDER BY lastname<BR>-----<BR>this does half of what i want...this does take the names apart and alphabatizes them, but i only need for the query to pull out the actuall letter of the alphabet..A, B, C, D..display the letter and then display the names that go under that leter.<BR>What do i need to add to my query to make this happen.<BR><BR>Thank you

  2. #2
    Join Date
    Dec 1969

    Default What are you actually getting??

    show a few records to give us an idea of what you are getting.

  3. #3
    Join Date
    Dec 1969
    Los Angeles, CA

    Default try this

    SELECT DISTINCT author, RIGHT(author, LEN(author) - CHARINDEX(&#039; &#039;, author)) AS lastname, LEFT(RIGHT(author, LEN(author) - CHARINDEX(&#039; &#039;, author)),1) as WhoMeYesYou<BR>

Posting Permissions

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