How to group records by using regular expression?
Hi Everybody I need to group records by regular expression Example: Table has column - names (just example) records are: idnames time 1 John, Max 12:15 2 Max, Jeff 15:55 3 Ken 20:45 I need to get grouped records by name Max in column names SELECT COUNT(*) FROM my_table GROUP BY reg_expression Could somebody help me? Thanx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to group records by using regular expression?
On Saturday, April 02, 2005 01:51, ON.KG wrote: Hi Everybody I need to group records by regular expression Example: Table has column - names (just example) records are: idnames time 1 John, Max 12:15 2 Max, Jeff 15:55 3 Ken 20:45 I need to get grouped records by name Max in column names SELECT COUNT(*) FROM my_table GROUP BY reg_expression Are you just looking for a count of names that contain max? SELECT COUNT(*) FROM my_table WHERE names REGEXP 'max'; or SELECT COUNT(*) FROM my_table WHERE names LIKE '%max%'; If you are really trying to group the names you can do this with a regex but this will only give you at most three rows (two if the column doesn't allow nulls). This is because the regex can only evaluate to true, false, or null. If you want just the ones that contain Max grouped together and everything else separate you could rig it with an if. SELECT names, COUNT(*) FROM my_table GROUP BY if(names REGEXP 'max', -1, names) The -1 would need to be replaced by a value that will not exist in the table. This is why I said it is rigged. Maybe if you could explain why you are trying to do this, someone could help you come up with a better solution. Could somebody help me? Thanx -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: How to group records by using regular expression?
Hi Everybody Thank you, Tom! I really trying to group the names But I think I found another way - I made PHP-script which groups results from database after selection And I suspect it will be a little complicated for MySQL to group all this data, 'cause variants in reg.exp. pattern is too large something like (yes|no|may be|can not|uknown|.) or MySQL are able to group it fast enough? faster than PHP? Thanx TC On Saturday, April 02, 2005 01:51, ON.KG wrote: Hi Everybody I need to group records by regular expression Example: Table has column - names (just example) records are: idnames time 1 John, Max 12:15 2 Max, Jeff 15:55 3 Ken 20:45 I need to get grouped records by name Max in column names SELECT COUNT(*) FROM my_table GROUP BY reg_expression TC Are you just looking for a count of names that contain max? TC SELECT COUNT(*) FROM my_table WHERE names REGEXP 'max'; TC or TC SELECT COUNT(*) FROM my_table WHERE names LIKE '%max%'; TC If you are really trying to group the names you can do this with TC a regex but this will only give you at most three rows (two if TC the column doesn't allow nulls). This is because the regex can TC only evaluate to true, false, or null. If you want just the ones TC that contain Max grouped together and everything else separate TC you could rig it with an if. TC SELECT names, COUNT(*) FROM my_table TC GROUP BY if(names REGEXP 'max', -1, names) TC The -1 would need to be replaced by a value that will not exist TC in the table. This is why I said it is rigged. TC Maybe if you could explain why you are trying to do this, someone TC could help you come up with a better solution. Could somebody help me? Thanx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]