Page 1 of 1

extracting the middle values of a string

Posted: Thu Jul 23, 2009 7:40 am
by Hillu
Hi, I have a table which contains employees. Each employee has a code. The code indicates the department the employee belongs to the year he joined the organization and a unique number. The format is three digits alphabets, two digits indicating the year and two digits the unique number. Like ‘ABC0503’.

I wanted the digits representing the year and I used this query.

$query = "SELECT SUBSTRING(code,4,5) AS acc
FROM tblEmployee
WHERE (SUBSTRING(code,1,3)= 'ABC' OR SUBSTRING(code,1,3)='XYZ' ";

$result = @mysql_query($query);
$nbrows_batch = @mysql_num_rows($result);
if ($nbrows > 0){
while($rowdata= @mysql_fetch_array($result)){
$empCode = $rowdata['acc'];
}//while($rowdata = @mysql_fetch_array($result))

But I am getting the whole 4 numeric characters. Any suggestiona?

Thanks.

Re: extracting the middle values of a string

Posted: Thu Jul 23, 2009 8:19 am
by Dynamis
Any reason why you can't just pull the whole code from the DB and then use:

Code: Select all

substr(code,2,2)
on the code to get the year?

Where the first 2 represents where to start and the second 2 indicates that you want to get the next 2 characters (the year).

Re: extracting the middle values of a string

Posted: Thu Jul 23, 2009 8:29 am
by Hillu
thanks a lot. It works.