3

I'm trying to get the length of a string in SQL (using Firebird version 2.x+). Whenever I select the length of a string it gives me the actual assigned maximum length of that string as opposed to getting the length of how many of the characters are taken in a record, as you can see here:

enter image description here

as you can imagine, this does not help me, as I can't order by the length, since I'm trying to order by an attribute that has a constant length assigned.

How would I achieve what I am trying to achieve? That is: getting the length of how many characters are taken in a string.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
aakk
  • 334
  • 4
  • 15
  • 2
    Is your column a CHAR or VARCHAR? – Charlie L Jun 30 '17 at 22:52
  • 3
    The data type is CHAR, which would make no sense, but the database was created by my professor, and one of the given assignments was to order by the name length. And I'm still not sure if he made a mistake there or if there is a way to count the taken characters in a CHAR – aakk Jun 30 '17 at 22:55
  • Don't have a copy of Firebird handy, but isn't there a Trim() or RTrim() or LTrim() scalar function available? – Ken White Jun 30 '17 at 22:56
  • 1
    https://firebirdsql.org/refdocs/langrefupd20-char-length.html - If you want to obtain the “logical” length, not counting the trailing spaces, right-TRIM the argument before passing it to CHAR[ACTER]_LENGTH. – Barbara Laird Jun 30 '17 at 22:56
  • 1
    Please post the equivalent of your screenshot as text, especially the query itself. – Mark Rotteveel Jul 01 '17 at 06:31

1 Answers1

4

As documented for char_length:

Notes

  • With arguments of type CHAR, this function returns the formal string length (i.e. the declared length of a field or variable). If you want to obtain the “logical” length, not counting the trailing spaces, right-TRIM the argument before passing it to CHAR[ACTER]_LENGTH.

The reasons for this is that char values are padded with spaces to the declared length, so in essence they are always of the declared length.

In other words you need to use:

char_length(trim(trailing from imeprodajalca))
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197