The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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

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

asked 16 Jul '12, 11:55

Martin's gravatar image

accept rate: 14%

edited 17 Jul '12, 02:57


Isn't that practical enough?

(16 Jul '12, 11:59) Volker Barth
Replies hidden

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
(16 Jul '12, 15:52) Breck Carter

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

(16 Jul '12, 16:57) Zote

@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?

(17 Jul '12, 00:36) Mark Culp

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

(17 Jul '12, 02:57) Martin

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

(17 Jul '12, 03:42) Volker Barth

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:

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

(17 Jul '12, 07:34) 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 :)

(17 Jul '12, 07:41) Breck Carter
showing 1 of 8 show all flat view

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.

permanent link

answered 16 Jul '13, 11:27

Glenn%20Barber's gravatar image

Glenn Barber
accept rate: 0%

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

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

Would be great if you can come up with something

(17 Jul '13, 06:37) Thomas Dueme...

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 :)

alt text

(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

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
showing 2 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 16 Jul '12, 11:55

question was seen: 998 times

last updated: 17 Jul '13, 09:01