What is the best practise for limiting text results?

[MCMartin]

What is the best practise for limiting text results to a certain number of chars?

e.g. Select left(name,20) from table

[VolkerBarth]

Isn't that practical enough?

[Breck_Carter]

I like kissing informing the user when the data's been shafted truncated:

SELECT IF LENGTH ( name ) > 20 
          THEN STRING ( LEFT ( name, 17 ), '...' )
          ELSE name
       END IF
  FROM table

[Former Member]

why string(left(name, 17), '...') if you can use left(name, 17) || '...' ?

[MarkCulp]

@Zote: good question - under the covers the string() expression operator concatenates its arguments into one big string, and the '||' expression operator does the same. I.e. with just two strings being pasted together the internal expression will look very similar. I doubt that there is an perceptable performance difference. If there were more than two strings/expression being concatenated then the string() operator would be a flatter expression - with one call to the internal dfo_string function which would have the N expressions as arguments - whereas the sequence of '||' operators would result in a left deep tree of dfo_concat function calls which in its extreme may be more expensive (but I have not tested/verified this).

We'll see what Breck says but I would expect that it is a personal preference?

[MCMartin]

It is practical, but I just wanted to know, if better approaches exists, or if using left has any drawbacks I am unaware of.

[VolkerBarth]

That should surely get the "nicest-practices" tag:)

[Breck_Carter]

Quick! What language am I coding in? Is it || or + or something else for string concatenation? (in SQL Anywhere it's both, but + really sucks because of those wrong-way conversions)

Where IS the steenking | key anyway?

Seriously, I spend my life coding STRING calls with (literally) hundreds of arguments, to generate HTML and JavaScript, and it just seems natural to use STRING all the time. Plus, it tells the reader exactly what is going on, and that is string concatenation, not the logical OR: http://www.w3schools.com/js/js_comparisons.asp

REALLY seriously: I will fight to the DEATH to protect my right to hang toilet paper away from the wall 🙂

[Breck_Carter]

No drawbacks whatsoever. SQL Anywhere has a whole bunch of really nice string handling functions and they have NEVER shown up as culprits in any execution profiling I've done.

Well, I can't speak for the regexp crap stuff, no one who ever worked with SNOBOL4 could ever stoop so low 🙂

[glenn_barber]

The above works but is fairly primitive as it chops words. The more elegant way to do that is to search backwards from the end of the desired truncation length and look for white space or punctuation and replace it with ellipsis (...) For larger text fields we sometimes supply the character count in parenthesis with the ellipsis.

[VolkerBarth]

Ah, "more elegant" will combine nicely with "regex" to search for whitespace, punctuation and the like...

I guess that will have a BreckRant® started:)

---

@Glenn: Don't get me wrong, I agree on your user-friendly approach - I just thought about how to solve that within SA, and read Breck's comments...

[glenn_barber]

I think we have some SA code we use for this - I'll try to post it later. THis is a very common requirement in reports when there can be a lot of text.

[thomas_duemesnil]

Would be great if you can come up with something

[Breck_Carter]

Here's what happens when *I* promise something in advance of delivering...

• Promise code sample that I "know" I have.

• Search fruitlessly for code sample that is lost, or perhaps once thought of but never actually written (I'm old, forgetful, etc).

• Write code sample from scratch, test, etcetera, finally deliver it.

• Rush to catch up with the work I was supposed to be doing.

Which is why I stopped promising stuff 🙂

[VolkerBarth]

Well, "I'll try to post it later" is not a real promise, I'd say - unless for folks with a very strong sense of duty...:)

[Breck_Carter]

I was not criticising, I was sharing advice... it's the reason I no longer say "this is the first article in a series" unless the whole series has been written... it's a jinx thing 🙂