Returning the first three characters from a string

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
icarpenter
Forum Commoner
Posts: 84
Joined: Mon Mar 07, 2005 8:12 am
Location: Kent, England

Returning the first three characters from a string

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

try somethign like this:

Code: Select all

<?
$newString = "elephant";
$len = strlen($newString);
$newString = substr($newString,0,$len-3);
echo $newString;
?>
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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.
User avatar
icarpenter
Forum Commoner
Posts: 84
Joined: Mon Mar 07, 2005 8:12 am
Location: Kent, England

Post by icarpenter »

Great thats got it...

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

Fantastic!!!

Thanks Ian
Post Reply