MySQL Trim Function Example

In this tutorial we will have a look at the Trim function of MySQL. MySQL has a very strong library we must use it, usually we format input our data using some dynamic languages like PHP, but what if there is a scenario we have to use MySQL database for it.

let the code talk...

MySQL Trim Method Signature

// First Type
String TRIM([{BOTH | LEADING | TRAILING} [STRING_TO_BE_REMOVED] FROM] str)

// Second Type
String TRIM([STRING_TO_BE_REMOVED FROM] str)

Returns the string string with all STRING_TO_BE_REMOVED prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. STRING_TO_BE_REMOVED is optional and, if not specified, spaces are removed.

lets understand it with examples.

Example

SELECT TRIM('  Tutorial   ') 
AS FormatedString;

Outputs: Tutorial

SELECT TRIM(LEADING 'jinni' FROM 'jinniTutorialjinni') 
AS FormatedString;

Outputs: Tutorialjinni

SELECT TRIM(BOTH 'jinni' FROM 'jinniTutorialjinni') 
AS FormatedString;

Outputs: Tutorial

SELECT TRIM(TRAILING 'jinni' FROM 'jinniTutorialjinni') 
AS FormatedString;

Outputs: jinniTutorial

Continue Reading...

MySQL Soundex Example

Soundex is a phonetic algorithm for indexing names by sound, as pronounced in English. The goal is for homophones to be encoded to the same representation so that they can be matched despite minor differences in spelling. The algorithm mainly encodes consonants; a vowel will not be encoded unless it is the first letter. Soundex is the most widely known of all phonetic algorithms, as it is a standard feature of MS SQL and Oracle, and is often used (incorrectly) as a synonym for "phonetic algorithm". Improvements to Soundex are the basis for many modern phonetic algorithms.

Soundexis a phonetic normalization function that was invented for the 1880 U.S. Censusto get around the problem of sorting information by last names with different spellings but similar or identical sounds, such as Smith and Smythe. Since then, it's become one of the more popular ways of searching for similar sounding names in genealogy and government applications.

In this tutorial we will use the MySQL SOUNDEX() function, it will very help for searching purpose i.e. if a user search for something and he/she spell wrong then we can use SOUNDEX() to understand the search term.

Method Signature for SOUNDEX

String SOUNDEX(String)

Example 1

SELECT SOUNDEX('tutorialjinni');

it will yeild T6425

comparing two words that sound same

SELECT STRCMP(SOUNDEX('sun'), SOUNDEX('son')) AS Result;

Output is 0 because both words sound same and for that reason there SOUNDEX() codes are same, for reference SOUNDEX code for both sun and son is S500

Limitations

• This function, as currently implemented, is intended to work well with strings that are in the English language only. Strings in other languages may not produce reliable results.
• This function is not guaranteed to provide consistent results with strings that use multi-byte character sets, including utf-8.

Continue Reading...

MySQL indexOf Function

In this tutorial we will learn how to find occurrence of a string in a string, to do this in MySQL database it provide us with a function INSTR(), it take two arguments first one is haystack or the string from which you wish to find the occurrence of other string, second string is the whom location we are interested in, Please note that the positioned returned will be the starting position of the needle.

Method Signature

int INSTR(haystack,needle);

Example 1

SELECT INSTR('foobarbar', 'bar');

Output : 4

Example 2

SELECT INSTR('xbar', 'foobar');

Output : 0


INSTR() is multi-byte safe, and is case sensitive only if at least one argument is a binary string.

Continue Reading...

Difference Between Dates in MySQL

Find difference between two dates is very easy to find out using simple SQL queries, suppose we want to check,the duration a user logged in.

SELECT 
TIME_TO_SEC( TIMEDIFF( COL_LOGOUT_TIME , COL_LOGIN_TIME ) ) 
AS INTERVAL_IN_SECONDS
FROM Users

Above query will return number of seconds between last logout and login time you can format it using PHP strtotime() and date() functions , COL_LOGOUT_TIME,COL_LOGIN_TIME are column names.

There is another scenario where one may wish to see the number of days since user last logged in.

SELECT 
DATEDIFF(CURDATE(), COL_LOGOUT_TIME) 
AS INTERVAL_IN_DAYS 
FROM Users 

above query will return number of days between current date and COL_LOGOUT_TIME.

Continue Reading...

Select Random Rows SQL

Sometimes ago i have a requirement to select random result from MySQL for my website dirjinni. I want to display Random articles at the bottom, as usual i search on google and find a solution which was some thing like...

-- NOT RECOMMENDED --

SELECT id,articles FROM articletable 
ORDER BY RAND() LIMIT 5

i was very happy to get the job done in very short period of time, but i forget there is no short cut to success :).

After 2 or 3 days i realized sometimes the results did not appeared, then i investigate the problem and found out that it is because of that MySQL "RAND()" function, the query become so expensive some times that the query failed and nothing is displayed (as i have shred hosting so resources are limited).

To Solve the problem i first get the MAX id from the table and then use PHP's rand() function to get 5 values from it and then put those values in the "IN" clause of SQL, my PHP code is ...

//...

$maxSQL="select max(id) as MAXIMUM form articletable";

$resultSet=mysql_query($maxSQL);
$arrayAsObject=mysql_fetch_object($resultSet);
$max=$arrayAsObject->MAXIMUM;

$v1=rand(1,$max);
$v2=rand(1,$max);
$v3=rand(1,$max);
$v4=rand(1,$max);
$v5=rand(1,$max);

$randomSQL="SELECT id,article FROM articletable 
            WHERE id 
            IN ($v1,$v2,$v3,$v4,$v5)";
 
//...

if you want more random number you can use array_rand function in PHP but this solve my problem and i hope it solve yours too.

Continue Reading...

Java MySQL Connection

MySQL is a great and powerful database and its combination with JAVA make is much more powerful. I would not go in long stories, i would tell you simple how to make a JDBC connection between JAVA and MySQL. for that you need java and mysql (of course) and MySQL JConnector (download it). once you download the jar file put it in the classpath of you project and use the following code to make connection and thats all you successfully make a JAVA MySQL connection.

import java.sql.*;
public class DBClass {
private Statement getStatement(){
Statement st=null;
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/DB_NAME", "USER_NAME", "SECRET_PASSWORD");
st=con.createStatement();
}
catch(Exception d){
System.out.println(d);
}
return st;
}

public ResultSet rs(String q){
ResultSet rs=null;
try{
rs=getStatement().executeQuery(q);
}
catch(Exception d){
System.out.println(d);
}
return rs;
}
public int updateQuery(String q){
int k = 0;
try{
k=getStatement().executeUpdate(q);
}
catch(Exception d){
System.out.println(d);
}
return k;
}
}

Continue Reading...

Store Arabic or Urdu in MySQL Using PHP

Often there are times when we have to store / retrieve a language other than the English language, so in order to do this and in a very easy manner i have found a way to solve this problem.

What i do , i simple map non English characters to numeric HTML equivalents and save them to MySQL and on retrieving form MySQL i map them back.

<?php
public function convertUrduForMySQL($urdu){

    $map = array(0x0, 0x2FFFF, 0, 0xFFFF);

    return  mb_decode_numericentity($urdu, $map, 'UTF-8');
    
    //Return encoded data that can be stored in MySQL
}

public function convertBackToUrduFromMySQL($encodedDataFromMySQL){

    $map = array(0x0, 0x2FFFF, 0, 0xFFFF);

    return mb_encode_numericentity($encodedDataFromMySQL, $map, 'UTF-8');

    // Convert back to Urdu
}

?>

and a simple usage example would be like it

public function usage(){

    $urdu="میں نے روبوٹ اور روبالہ پر آپ کا تجزیہ دیکھا ہے";
    
    $encodedDataFromMySQL=$this->convertUrduForMySQL($urdu);

    echo $encodedDataFromMySQL."<hr>";

    $urdu=$this->convertBackToUrduFromMySQL($encodedDataFromMySQL);

    echo $urdu;
}

output of the above code will be

میں نے روبوٹ اور روبالہ پر آپ کا تجزیہ دیکھا ہے


میں نے روبوٹ اور روبالہ پر آپ کا تجزیہ دیکھا ہے

although there are other ways to do this but to me it is very simple and quick and hope same for you :)

Continue Reading...