Archive for category Mysql

Mysql function to soundex match a word in a multi word string






soundex is a very useful mysql function when we try to compare 2 words if they sounds similar. Here is the official manual for the function. Soundex is pretty easy to use when we are comparing 2 words. For example, check the following query: select if(soundex(‘Rösle’)= soundex(‘rosle’), 1, 0); It’ll return 1 as both the […]

, ,

9 Comments

Mysql rounding for currency format






Most of the time we are used to format float numbers into currency format by PHP (or any front end language) using functions like fprintf(). If we could grab the result formatted from the db, we won’t need to use front end formatting. Mysql function format() is the function we need. Lets say we have […]

,

4 Comments

Build dropdown from Mysql SET or ENUM fields






We need to populate dropdown fields from Mysql SET or ENUM fields pretty often. So here is how I use to do it. Lets say ‘test’ is a table that has a field, ‘agree’ of type ‘SET’ having values ‘Yes’ and ‘No’. On the front end of my application I want to show a dropdown/select […]

,

12 Comments

Mysql function to find distance between two places using lat/long






Sometimes we need to find out list of places that are within a certain radius from a center place where coordinates of the places are saved in the database. Now we have 2 solutions for this – either loop through all the places find the distance from the center point and keep the places that […]

,

33 Comments

Find duplicate entry in table






This is a very handy query all the db programmers and db admins need everyday. Lets say, we have a table “test” with 2 fields “id” and “name”. We need to find out which names are duplicated in the table. So here is the query: select name, count(*) from test group by name having count(*) […]

, ,

6 Comments