28 Jan 09 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:
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 |
Tags: mysql
01 Dec 08 MySQL Casting Gotcha
Works
SELECT 409.95 = (1 + 408.95)
Doesn't
SELECT 409.95 = (a + b)
Works
SELECT 409.95 = CAST((a + b) AS decimal)
27 Nov 08 SQL Beautifier
I've spent some time searching and I've finally found a good tool for auto-formatting SQL! Simply paste in your fugly code, make any configuration tweaks, post and *bam*. Beautiful SQL.
Great for those ColdFusion dumps.
There's also a desktop version, but I didn't check it out.
Tags: coldfusion, mysql
15 Nov 08 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:
<cfset db_util = CreateObject("component","db_util").init(dsn = "datasource_name")/> <cfoutput>Looking for column <strong>#search_term#</strong></cfoutput> <cfdump var="#db_util.FindColumn(search_term)#"> <cfoutput>Looking for value <strong>#search_term#</strong></cfoutput> <cfdump var="#db_util.FindValue(url.looking_for)#">
Download ColdFusion MySQL Search Utility!
PS: it won't search numeric columns if you aren't searching for a number.
Tags: coldfusion, mysql, tools
20 Oct 08 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.
Tags: mysql
