MYSQL strings function can make your life easier for you.You can use combination of string function to get the complex result needed in some project.String function in mysql helps us to extract what we want from a string and that can avoid lot of code.The common String functions available in MYSQL can be found on the link http://dev.mysql.com/doc/refman/5.1/en/string-functions.html
Use of MYSQL String function.
consider the following table which contains caseId and you have to extract maximum number at the last of caseId, those belong to id 1.
| caseId | id | Value |
| JET/2005-2006/1 | 1 | 15 |
| JET/2005-2006/2 | 1 | 25 |
| JET/2005-2006/1 | 2 | 15 |
| JET/2005-2006/2 | 1 | 2 |
| JET/2005-2006/2 | 2 | 12 |
| JET/2005-2006/3 | 1 | 5 |
you can use the following query to get the result.
SELECT max(substring(caseId,locate(”/”,caseId,locate(”/”,caseId)+1)+1)) as maxCaseId FROM `test` WHERE id=1
The result of the query is as follows.
| maxCaseId |
|---|
| 3 |
This query work as follows.
inner locate function get’s first occurrence of “/” [let's 4].and outer locate function get first occurrence of “/” [let's 17] in the string starting from position 4+1=5.so finally in subString function will give all the string values after position 17+1=18.So by this we will have all numbers those belongs to id 1.now finally max function will get max number within all the numbers found by substring function.
if you want to get the maximum integral value, you got to cast it to an integer - otherwise, on having a caseId value like JET/2005-2006/23, it would still return 3 as maximum & not 23.
SELECT
MAX(0 + SUBSTRING(caseId, LOCATE(’/', caseId, LOCATE(’/', caseId) + 1) + 1)) AS maxCaseId
FROM `test` WHERE id = 1;
SELECT
MAX(0 + RIGHT(caseId, LOCATE(”/”, REVERSE(caseId)) - 1)) AS maxCaseId
FROM `test` WHERE id = 1;
even reversing strings can avoid looping of locate.
SELECT MAX(RIGHT(caseId, LOCATE(”/”, REVERSE(caseId)) - 1)) AS maxCaseId FROM `test` WHERE id = 1So This is just an example to explain uses of string functions available in MySql. There may be lot’s of other situation where you can use string function provided by mysql and get your work done rather than getting result by writing complex logic in programming.
