Page 1 of 1

Returning the first three characters from a string

Posted: Mon Aug 01, 2005 11:41 am
by icarpenter
Hi does anyone know how to remove the last three characters from a column with an undifined string length in SQL...

Example...

SELECT * from animals

Would currently return:- elephant,monkey,frog

I would like it to return:- eleph,mon,f

Any help will me much appreciated Ian

Posted: Mon Aug 01, 2005 12:02 pm
by Burrito
try somethign like this:

Code: Select all

<?
$newString = "elephant";
$len = strlen($newString);
$newString = substr($newString,0,$len-3);
echo $newString;
?>

Posted: Mon Aug 01, 2005 12:06 pm
by timvw
Most SQL products have a SUBSTR function (Sometimes the help of STRLEN is required)

Fe MySQL:

Code: Select all

SELECT SUBSTRING(column, 1, LENGTH(column) -3)
Edit: Btw, if LENGTH(column) -3 < 3 you'll have to use 3. But with the IF function you can handle that.

Posted: Tue Aug 02, 2005 2:41 am
by icarpenter
Great thats got it...

I used SUBSTRING(t3.dbpostcode,1, LENGTH(t3.dbpostcode)-3)

Fantastic!!!

Thanks Ian