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? Link to comment https://forums.phpfreaks.com/topic/80457-mysql-query-help-please/ 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 Link to comment https://forums.phpfreaks.com/topic/80457-mysql-query-help-please/#findComment-407923 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 Link to comment https://forums.phpfreaks.com/topic/80457-mysql-query-help-please/#findComment-407930 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? Link to comment https://forums.phpfreaks.com/topic/80457-mysql-query-help-please/#findComment-407974 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. Link to comment https://forums.phpfreaks.com/topic/80457-mysql-query-help-please/#findComment-408280 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. Link to comment https://forums.phpfreaks.com/topic/80457-mysql-query-help-please/#findComment-408315 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. Link to comment https://forums.phpfreaks.com/topic/80457-mysql-query-help-please/#findComment-411777 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. Link to comment https://forums.phpfreaks.com/topic/80457-mysql-query-help-please/#findComment-411954 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.. Link to comment https://forums.phpfreaks.com/topic/80457-mysql-query-help-please/#findComment-412840 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.