Getting just some of the column data (txt)

[[o_O]]

It's like this:

Product Table
-id
-name
-price
-pic
-description


Now the description for each product is generally a few paragraphs.

On my index I would like to display 'featured products' with their pic, price, name and a tidbit of info. So i would like to draw this tidbit from the description column but of course not all of it because the whole description would be too big. just a few lines with a '....' added to the end indicating their is more detail.


How can i do this? is there some sort of function i can use to limit how much of the descrpition txt is pulled?


[o_O]

[oracleguy]

I'm sure you can do it in the SQL query but you also can do it in ASP.

myVar=Mid(rs("description"),1,500) & "..."

This will truncate it down to the first 500 characters.

[aCcodeMonkey]

Just add Left(description,500) AS Description to your query.

e.g.

SELECT id, name, price, pic, left(description,500) AS description
From myTable
WHERE .....

The query will only return the snippit that you want. This also faster that using code to trim the recordset field.


Hope this helps :cool:

[[o_O]]

Cheers both. Exactly what I was looking for :)

[whammy]

Yup, as Mr. Mitchell said, this is definitely faster when done in the SQL Query itself instead of using ASP code. :)

This isn't exactly related, but might be useful... when you MIGHT have a null value in a database, but you perhaps want another value returned if the value in the database is null, try this (this depends on the datatype, of course!):

for a numeric datatype:

SELECT ISNULL(counter, 0) FROM tablename

for a text datatype (i.e. varchar):

SELECT ISNULL(myfield, 'nothingtoseehere') FROM tablename

Then instead of SQL returning a NULL value, you will get another default value if the field is NULL.

In the case of the varchar field, for instance, you could use ' ' as the default return value, to write a non-breaking space into a datagrid (so the HTML table doesn't choke in older browsers).

Hope this helps you in the future - I know it will. :)

[[o_O]]

Yeah I'll use that. :cool:

[aCcodeMonkey]

Whammy.

You can use Convert(datatype, query function) in the query to convert numberic datatypes to a varchardatatype.

Example:
SELECT id, name, CONVERT(varChar, IsNull(price,' ') AS price, pic, left(description,500) AS description
FROM myTable
WHERE .....

This is how I force SQL to prevent empty HTML cells that I am not calculating.



:cool:

[[o_O]]

At the moment this cuts off at the 500 character. Obviously this means it will many times cut a word short or look like this:

This thing is really great it has a h...

Not really a big deal but can you actually tell it to cut off at the nearest space or <br> tag?

[oracleguy]

You bet you can :)

Use this function i just threw together:


Function Shorten(txt)
Dim strTemp
strTemp=mid(txt,1,500)
If asc(mid(strTemp,500,1))<>32 Then
Do
strTemp=Left(strTemp,Len(strTemp)-1)
Loop While asc(mid(strTemp,len(strTemp),1))<>32
End If
strTemp=Left(strTemp,Len(strTemp)-1)
Shorten=strTemp & "..."

End Function


See it in action:

www.kurannet.com/asptest/shorten.asp

[[o_O]]

Good **** ;) !