Tuesday, July 15, 2008

MySql: substr of string using substring_index()


Share at Facebook

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




4 comments:

Anonymous said...

Thanks,
it helped me out today to separate "lastname,firstname" into 2 .
osgeek.blogspot.com

Anonymous said...

HI nice. but my need is
www.amc.mywebsite.com if i want to only amc throught select how can i proceed ? any assist

Gino said...

you could do this to get only the amc:
select substring_index(substring_index('www.amc.mywebsite.com','.',2),'.',-1);

joshua said...

Thanks. Needed to match 6 or 10 digit postal codes in one table with 6 digit postal codes in another table. Worked perfectly!