Page 1 of 1

Just the first 100 characters of a query

Posted: Thu Oct 06, 2005 3:48 pm
by VKX
So I have some text in a mysql database value, but I don't want to display all of it. What's the command to only return the first so many letters from it?

Posted: Thu Oct 06, 2005 4:16 pm
by Burrito
I've never done it right off of my query, but you could try substr()

Posted: Thu Oct 06, 2005 4:20 pm
by dyonak
Would it be acceptible to retrieve the whole entry and then use PHP to only display the first 200 characters? If so something like this should work:

Code: Select all

$dbEntryTeaser = substr($dbEntry, 0, 100);
echo $dbEntryTeaser;

Posted: Thu Oct 06, 2005 4:21 pm
by dyonak
double postage 8O

Posted: Thu Oct 06, 2005 4:24 pm
by Burrito
but dissimilar enough in that mine suggested using the mysql function substr() and yours the php function...as I said though, I've never used the mysql version...and not sure I ever will 8)

Posted: Thu Oct 06, 2005 4:38 pm
by VKX
Perfect! Thanks guys.

Posted: Thu Oct 06, 2005 6:06 pm
by pickle
You CAN do it in your query

From the manual:

Code: Select all

SUBSTRING(str,pos)  , SUBSTRING(str FROM pos) , SUBSTRING(str,pos,len)  , SUBSTRING(str FROM pos FOR len)

mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'        
mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'        
mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'
SUBSTR() is a synonym for SUBSTRING(), added in MySQL 4.1.1.
So if you want the first 100 characters, you could do:

Code: Select all

SELECT
  SUBSTRING(long_field_value,0,100)
FROM
  myTable

Posted: Thu Oct 06, 2005 6:12 pm
by feyd
last I saw, first character was one not zero for SUBSTRING()

Posted: Thu Oct 06, 2005 7:10 pm
by VKX
What if I want it to end on an even word instead of just a letter?

Posted: Thu Oct 06, 2005 7:15 pm
by feyd
SUBSTRING_INDEX() or
Useful Posts wrote:Chopping of text without losing words: chopping of text