Jump to content


Photo

Distinct Substring Query


  • Please log in to reply
1 reply to this topic

#1 skeezmo

skeezmo
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationPhoenix!

Posted 31 July 2006 - 01:35 AM

Hello,

I have a large list of songs and artists and I am creating a playlist.  At the top, I have the user select a letter which will then direct them to artists that start with that letter.

The problem I am running into is for artists that start with a number should be going under a separate category 0-9 and '.  But instead they are going as individual numbers in the list.

The query I am running for this is: 
SELECT distinct substr(artist, 1,1) as browse FROM songlist WHERE songtype = "S" or songtype = "C" ORDER by artist asc

I have tried adding into the where clause AND songlist.browse not like '1%') etc.

My question is, can I run a query and exclude numbers from returning in the result?

Does anyone have any suggestions that I could use to accomplish this?

Thank you in advance to anyone who may be of help.

Hess Smith
Skeezmo



The truth about Windows Programming: Windoze M.E. More Errors

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 August 2006 - 02:26 PM

You should probably be able to use a regex and exlude the appropriate character class (UNTESTED):

SELECT distinct substr(artist, 1,1) as browse FROM songlist WHERE songtype = "S" or songtype = "C" AND LEFT(artist,1) REGEXP "^[0-9']" ORDER by artist asc

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users