|
What is the best practise for limiting text results to a certain number of chars? e.g. Select left(name,20) from table |
|
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. 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...
(16 Jul '13, 11:40)
Volker Barth
1
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.
(16 Jul '13, 11:48)
Glenn Barber
Replies hidden
Here's what happens when *I* promise something in advance of delivering...
Which is why I stopped promising stuff :)
(17 Jul '13, 07:07)
Breck Carter
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...:)
(17 Jul '13, 07:13)
Volker Barth
1
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 :)
(17 Jul '13, 09:01)
Breck Carter
|
Isn't that practical enough?
I like
kissinginforming the user when the data's beenshaftedtruncated:SELECT IF LENGTH ( name ) > 20 THEN STRING ( LEFT ( name, 17 ), '...' ) ELSE name END IF FROM tablewhy string(left(name, 17), '...') if you can use left(name, 17) || '...' ?
@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?
It is practical, but I just wanted to know, if better approaches exists, or if using left has any drawbacks I am unaware of.
That should surely get the "nicest-practices" tag:)
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 :)
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
crapstuff, no one who ever worked with SNOBOL4 could ever stoop so low :)