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.
So 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.
Hey – just came across your blog !
Anyway, reversing the string helps in looking for the last occurrence without looping it.
SELECT MAX(RIGHT(caseId, LOCATE(“/”, REVERSE(caseId)) – 1)) AS maxCaseId FROM `test` WHERE id = 1
By: anjanesh on February 25, 2008
at 1:13 pm
PS: 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;
By: anjanesh on February 25, 2008
at 2:32 pm
Here’s a challenge…
Let’s say i have the following strings
455651243
4556556
455659899656
4556522
45565153
4556578876
45565111111
45565222222
45565433333
What would you guys execute in order to get the left most common string of the group?.. in this case it would be 45565.
Reagrds
Pete
By: Pedro on July 5, 2008
at 8:04 am
zandoooooooo
By: kailash kumar on June 16, 2009
at 12:12 pm