Stump of a Muffin

Category: MySQL Tools

A pile of favourites I’ve collected to help my MySQL development easier.

MySQL CASE for 4.0

SELECT `r_mls_number` ,`r_address` ,`r_city` ,`r_area` ,`r_sub_area_community` ,`r_sub_area_community_desc` ,`r_list_price` ,`r_sold_price` ,`r_orig_price` ,`r_type_of_dwelling` ,`r_status` ,`r_list_date` ,`r_sold_date` ,`r_total_bedrooms` ,`r_total_baths` ,`r_floor_area_total_finished` ,`r_floor_area_unfinished` ,`r_last_trans_date` ,`r_street_name` ,`r_street` , `r_record_delete_flag` ,  CASE   WHEN r_street_dir = ‘Northwest’ THEN ‘NW’ WHEN r_street_dir = ‘Northeast’ THEN ‘NE’ WHEN r_street_dir = ‘Southwest’ THEN ‘SW’ WHEN r_street_dir = ‘Southeast’ THEN ‘SE’ ELSE ” END  AS street_dir , r_street_typeFROM rea WHERE…

MySQL – LEFT

I needed a way to trim the three left side characters.  I’ll save you the long boring story about why – here’s the how: SELECT LEFT( price , CHAR_LENGTH(  price ) -3  ) AS price FROM products This gave me 150 instead of 150000.

TRIM and MySQL

Working on a wireless iPhone application right now.  With such limited ‘real estate’ for viewing on wireless devices I’ve decided to trim off the 3 zeros. I want $575,000 to show as 575.  At first I didn’t think this would be a big deal.  PHP has the nifty little substr() function and I was pretty…