CODEWITHSUNDEEP
phpmysqlsql
sasidhar
sasidhar

Reputation: 7752

retrieving only part of the record in database

I have a database which contains some topics each with a title and some description. In the title page, I have to display all the titles and a few lines of description for that topic. When a user clicks on one topic then he is taken to a page where he can find the entire description. Exactly like stackoverflow site, where description of each question is displayed in short.

I was wondering is there a way we can retrieve such short description from the database directly or should the front end parse the retrieved record and truncate it accordingly? Is there a way to query for a short version of the record from the database directly. I am using MySql as my database and php to display web pages.

Upvotes: 3

Views: 436

Answers (6)

xkeshav
xkeshav

Reputation: 54016

Best solution:

SELECT SUBSTRING_INDEX('your string', ' ', 10);

this will return your string upto 10 spaces.

Note: SUBSTRING cause you to cut off words right in the middle as suggested by @BiggsTRC

Reference

Upvotes: 2

sasidhar
sasidhar

Reputation: 7752

  select LEFT (description, CHARINDEX(' ', description, 50) ) from topics

this seems to address my problem correctly... any way thanks for all the answers.

Upvotes: 1

IAmTimCorey
IAmTimCorey

Reputation: 16757

I would say the answer is found in this post: SQL Server substring breaking on words, not characters

You wouldn't want to just limit by a string length, since then you might cut off a word right in the middle. Instead, you would want to set your max length and then move back to the place where there is a space.

Another option would be to run this once and then update a field in the table that stores the truncated text. That way you could limit your extra SQL calls and yet still have the nicely-formatted summary text.

Upvotes: 2

Matthew Clark
Matthew Clark

Reputation: 571

You can limit the value you get back with a cast like this: select title, cast(description as varchar(300)) from table

or you could store a less abrasive truncated description as a smallDescription or whatever in the table as well(then make your frontend let you mark it and it dissects it for you^^).

Upvotes: 0

sasfrog
sasfrog

Reputation: 2460

Do you mean a chunk of the text from a text field?

Like select left(LongTextField,200) from myTable

Upvotes: 0

Alex Pliutau
Alex Pliutau

Reputation: 21957

SELECT SUBSTRING(`title`, 0, 100) FROM `topics`

Upvotes: 3

Related Questions