Tag Archives: mysql

Today’s Favorite MySQL Function: GROUP_CONCAT

Don’t know why I never thought to google for something like this before. There probably aren’t too many good uses for it, but it saved me a bunch of time today so I thought I’d share:

GROUP_CONCAT

Running a query like the one below, would return all the values from my sub-select as a comma delimited list so this…

SELECT products.namename,
        GROUP_CONCAT(options.option) AS options
FROM products
     INNER JOIN options
     ON          options.productID = products.productID

Could return something like this:

name options
t-shirt green, blue, red, small, medium, large
hat cowboy

ColdFusion MySQL Search Utility

There’s a legacy app I work on that currently requires adding 4 columns and a couple rows (in different tables) every time you add a row to another table. It doesn’t happen often and it would be a nightmare to refactor so I wrote little script that would search the database for any columns named ‘x’ as well as any fields with a value of ‘x’ and return an array of the offending tables.

I cleaned it up a little bit and cfc-ized it in case I ever have to do anything similar. I haven’t tested it at all aside from the one time I ran it today so use it at your own risk. It’s really simple to use, you just need to pass in the ColdFusion datasource name and the search term.

Example Usage:



Looking for column #search_term#



Looking for value  #search_term#

Download ColdFusion MySQL Search Utility!

PS: it won’t search numeric columns if you aren’t searching for a number.

MySQL Capitalize Function

I wrote a little MySQL function to capitalize the first letter of every word in a string. I thought I’d share since I wasn’t able to google for it.

CREATE FUNCTION CAP_FIRST (input VARCHAR(255))

RETURNS VARCHAR(255)

DETERMINISTIC

BEGIN
	DECLARE len INT;
	DECLARE i INT;

	SET len   = CHAR_LENGTH(input);
	SET input = LOWER(input);
	SET i = 0;

	WHILE (i < len) DO
		IF (MID(input,i,1) = ' ' OR i = 0) THEN
			IF (i < len) THEN
				SET input = CONCAT(
					LEFT(input,i),
					UPPER(MID(input,i + 1,1)),
					RIGHT(input,len - i - 1)
				);
			END IF;
		END IF;
		SET i = i + 1;
	END WHILE;

	RETURN input;
END;

So running the following code...

SELECT CAP_FIRST(
	'this is totally like   @ TEST 1 right!' 
)

Returns the string "This Is Totally Like @ Test 1 Right!"

I would rather have regex'd it, but I couldn't find any sort of regex replace function in the docs.