d_barszczak Posted December 6, 2007 Share Posted December 6, 2007 Hi all, I have a column that stores id numbers for courses called idnumber i.e. THS-ICT-001 THS-ICT-002 THS-MATHS-001 THS-MATHS-002 I want to use a DISTINCT query to return THS-ICT, THS-MATHS without the final -001. Is this possible? Quote Link to comment Share on other sites More sharing options...
rajivgonsalves Posted December 6, 2007 Share Posted December 6, 2007 try this SELECT DISTINCT substring(idnumber,1,LENGTH(idnumber)-4) FROM tablename Quote Link to comment Share on other sites More sharing options...
d_barszczak Posted December 6, 2007 Author Share Posted December 6, 2007 Cheers, Are you some sort of mysql god? lol Quote Link to comment Share on other sites More sharing options...
fenway Posted December 6, 2007 Share Posted December 6, 2007 I'll obviously mention that you shouldn't be storing non-atomic values in a single field... but for this to be robust, you should probably be looking for the last "-"... what if the number is longer than 3 characters? Quote Link to comment Share on other sites More sharing options...
d_barszczak Posted December 6, 2007 Author Share Posted December 6, 2007 Your absolutly right but... The database is a Moodle Installation that will be transfered to a bigger solution. The id number is just a tag to make it easier to import. Plus the THS is the initials of the company and MATHS would be the department and the 001 is the unique id and i know there will never be 999 maths courses before we transfer. Quote Link to comment Share on other sites More sharing options...
fenway Posted December 6, 2007 Share Posted December 6, 2007 Your absolutly right but... The database is a Moodle Installation that will be transfered to a bigger solution. The id number is just a tag to make it easier to import. Plus the THS is the initials of the company and MATHS would be the department and the 001 is the unique id and i know there will never be 999 maths courses before we transfer. If it's a temporary hack, then no problem... I just tend to find that "temporary" often lasts a lot longer than expected, and the extra work at the outset is insignificant in the long run. Just my $0.02. Quote Link to comment Share on other sites More sharing options...
d_barszczak Posted December 11, 2007 Author Share Posted December 11, 2007 No worries you have helped me a lot in the past and it's always worth listening to your advice. Your right though i have learned that tempoary hacks can be left longer that you expect them too. Also i did say it was a moodle installation which means that all input relys on teachers so getting them to populate a vle with 999 letters is hard enough never mind 999 courses. Quote Link to comment Share on other sites More sharing options...
fenway Posted December 11, 2007 Share Posted December 11, 2007 Also i did say it was a moodle installation which means that all input relys on teachers so getting them to populate a vle with 999 letters is hard enough never mind 999 courses. Didn't get that part... ;-) Good luck. Quote Link to comment Share on other sites More sharing options...
d_barszczak Posted December 12, 2007 Author Share Posted December 12, 2007 Guess you need to work in education to get that one. Basically teachers want a vle/mle but can any be bothered to supply content for it... nup.. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.