Truncating Queried Text 1

[highv0ltage]

How would I truncate the text from a field in a database.?Would that be done at the query or the ouput of the variable? Also, even though I want it truncated, I would like the full text to be used as the ALT for the link. Thanks.

 

[imstillatwork]

You can get both from the query.


SELECT text AS fulltext, LEFT(text,200) AS trunctext FROM...

But the truncated text could be in the middle of a word, unless you know regex / sql magic that I am no good at

So you could truncate to the word with CF

SELECT text FROM...

<cfoutput query...>
#text# is full text

#listDeleteAt(ListLen(left(text,200)," ")," ")# Short Text, cut off at the word.

probably better ways to do it? It is friday...






Kevin

http://www.lot-o-nothin.com/cfml/

 

[highv0ltage]

You get around Kevin. Thanks again for the help. I copied and pasted what you type, but I got an error.

The value of the parameter 2, which is currently " ", must be a int value.

 

[r937]

there's a way to cut it off on a whole word in mysql, if you're interested -- the substring_index function

r937.com | rudy.ca

 

[TamedTech]

Ah ... you've been keeping that quiet Rudy .. I've been after somthing like that for ages lol ... all my current article 'Briefs' on news columns and things cut off half way through a word ... that'll come in handy.

Rob

 

[r937]

well, i was a wee bit hesitant to speak up, since highv0ltage neglected to mention which dbms this was for

i *heart* SUBSTRING_INDEX -- you should see how easy it is to pull out the domain from a url which might look like either

http://www.example.com/index.html

or

http://foo.bar.fap.example.com/folder/folder/index.html

try doing that with charindex

r937.com | rudy.ca

 

[TamedTech]

It sounds fantastic,

Have you got a more thorough explanation at to how it might be used to truncate a load of text from a database field? .. just for us hopeless amatures ;-)

Ta,

Rob

 

[r937]

example: choose the first seventeen whole words --

substring_index(mytextcolumn,' ',17)



r937.com | rudy.ca

 

[TamedTech]

Good man Rudy ... you've earned yourself a Purple star on that one.

Thanks again,

Rob

 

[imstillatwork]

The value of the parameter 2, which is currently " ", must be a int value.

#listDeleteAt(text,ListLen(left(text,200)," ")," ")#

But listen to rudy.

Kevin

http://www.lot-o-nothin.com/cfml/

 

[TamedTech]

Thanks Kevin,

Whats the purpose of your #listDeleteAt(text,ListLen(left(text,200)," ")," ")# function?

I've just used Rudy's example of substring_index(mytextcolumn,' ',17) and left that second parameter as " " and it seems to be working fine. Is that the parameter you were refering too?

Thanks,

Rob

 

[r937]

what's the purpose of using the listDeleteAt expression at all, if you have pulled out a whole number of words?

r937.com | rudy.ca

 

[imstillatwork]

The SQL method is the way to go...

But if you don't...

What I posted takes 200 chars of the text, and cuts off the last word, which may or may not be a whole word.

The value I was refering to was in reply to highvoltage's replay about what I posted.



Kevin

http://www.lot-o-nothin.com/cfml/

 

[highv0ltage]

Sorry, I didn't mention the database being used. I just wasn't sure if this could be done from the query. I am using an Oracle database. Because of this, I'll probably need to use the #listDeleteAt right?

 

[r937]

oracle? you shoulda said so

i would do something like this (don't have oracle to test on) --

select left(daField,instr(daField,' ',1,17)-1)

finds the 17th blank and takes everything to the left of it




r937.com | rudy.ca

 

[highv0ltage]

Thanks for everyones help. I found a different way, tell me what you think.

SELECT substr(daName,1,25)

This seemed to work fine, but I don't know if it's kosher.

 

[r937]

oh, it's wonderful

but it could quite easily chop a word off in the middle

r937.com | rudy.ca

 

[highv0ltage]

Yeah, that's fine. I'll just incorporate an elipse so people no there's more to the title. And I have a title on each link so they get a tooltip.

 

[highv0ltage]

OK, I'm back. Realized I don't know how to check to if a varible has x amount of characters. I need this so I can run it through a cfif and add an elipse if necessary.

 

[r937]

actually your problem is worse than you think it is

if you have a field that's 21 characters long, then taking

substring(field,1,25)

will return the entire field, and you do not want to add an ellipsis in that case

also, an ellipsis that indicates there is something more looks silly right behind a word that has been trunca...

r937.com | rudy.ca