MySql: substr of string using substring_index()

I was in need to find a way to get the left side of a string after a specific character. But didn't know how to do until knowing substring_index() function of MySql. This function can strip the string for specific character.

Consider our string is

"test.substring.index"

And we need to get the left portion(test) where the first dot(.) appears. Using this function the sql query will be as below.

select substring_index('test.substring.index','.',1);

Here the first parameter is the string, second parameter is the character, and 1 means the whole string before the 1st dot. If we use 2, then output will be the string before 2nd dot. If there is no 2nd dot found, then it will be the whole string as output. Below will be the output for 1-3.

1,test
2,test.substring
3,test.substring.index

If you need to get the portion from right side, then you need to do -1 or -2 or -3 where -1 is first one from right side.

-1,index
-2,substring.index
-3,test.substring.index

If you want, you can use the function inside select/update query. For example a select query will be as below where the table name LIST, and column name is EMP_DESC.

select substring_index(`EMP_DESC`,',',1) from LIST;

You can read other articles from by blog. Here it is http://icfun.blogspot.com/search/label/mysql