0

I'm looking for the equivalent of Select LEN(1234) from x Return 4 for FlameRobin for my Char field.

All I can find is char_length which returns the fields max length not the contents of the field.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
SQLTemp
  • 169
  • 7
  • The content of a `CHAR` field **is always the max length**, padded with spaces if the assigned value was shorter. If you don't want that, you should use a `VARCHAR` field. Related, possibly duplicate [Getting the length of a string in SQL](https://stackoverflow.com/questions/44855739/getting-the-length-of-a-string-in-sql) – Mark Rotteveel Nov 24 '20 at 12:32

2 Answers2

1

Because that is the difference between SQL datatypes CHAR (fixed length, always right-padded with spaces, like in DBF and other tabular formats of that age) and VARCHAR (variable-length, may be shorter than max length).

And your query is NOT a query you are really using!
The query you suggest DOES return exactly 4 in Firebird.

db<>fiddle here

select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version
     , rdb$character_set_name
from rdb$database;

VERSION | RDB$CHARACTER_SET_NAME                                                                                                      
:------ | :---------------------------------------------------------------------------------------------------------------------------
3.0.5   | UTF8                                                                                                                        

Select char_LENgth(1234) from rdb$database

| CHAR_LENGTH |
| ----------: |
|           4 |

create table T (
  i integer,
  c char(20),
  v varchar(20)
)

✓

insert into T values (1234, 1234, 1234)

1 rows affected

select * from T

   I | C                                                                                | V   
---: | :------------------------------------------------------------------------------- | :---
1234 | 1234                                                                             | 1234

Select 
  char_length(1234) as const
  , char_length(i) as int_to_char
  , char_length(c) as fixed_char
  , char_length(v) as var_char
  , char_length(trim(c)) as char_t
  , char_length(cast(trim(c) as varchar(20))) as char_t_v
  , char_length(trim(cast(c as varchar(20)))) as char_v_t
from T

CONST | INT_TO_CHAR | FIXED_CHAR | VAR_CHAR | CHAR_T | CHAR_T_V | CHAR_V_T
----: | ----------: | ---------: | -------: | -----: | -------: | -------:
    4 |           4 |         20 |        4 |      4 |        4 |        4
Arioch 'The
  • 15,799
  • 35
  • 62
1

This is exactly what should happen. If you store "HELLO" in a CHAR(20) field, you will get a 20 character string on output (it might be trimmed somewhere along the path, so you don't realize that the initial size is always padded to, or truncated to, 20).

Either use VARCHAR type, or you'll have to do something like CHAR_LENGTH(TRIM(FieldName)) to get the "perceived length" of the string.

LSerni
  • 55,617
  • 10
  • 65
  • 107
  • 1
    Your note doesn't make sense, and is incorrect: The function you link is `OCTET_LENGTH`, which counts bytes, not `CHAR_LENGTH`, which counts characters. Also note that you linked to the Firebird 2.1 Language Reference Update, it would be better to link to the more up-to-date [Firebird 2.5 Language Reference](https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html). – Mark Rotteveel Nov 24 '20 at 12:30
  • @MarkRotteveel You are right. I'm more than a little behind on my Firebird. From the docs, from version 2.0 Firebird solves the encoding problem by dividing by four all byte limits on chars ( https://ib-aid.com/download/docs/firebird-language-reference-2.5/fblangref25-datatypes-chartypes.html ). I was probably mis-remembering some caveat from way back. Fixed answer. – LSerni Nov 24 '20 at 14:25
  • You might be thinking of things like reading a UTF8 column with connection character set NONE and a client that doesn't correctly handle the column specific character set (some clients then think a `CHAR(4) CHARACTER SET UTF8` is a `CHAR(16)`). – Mark Rotteveel Nov 24 '20 at 15:37
  • @MarkRotteveel still that `OCTET_LENGTH` comment was of interest, it covered some niche case i ignored. Now that you two mainstreamed the answer - it became just a copy of my answer, just posted later and with less details. But previously it used to add to it. Though the added value was kind of obscure and very rare case, it was. – Arioch 'The Nov 25 '20 at 11:06
  • @Arioch'The It was irrelevant to the question and only confused the issue. – Mark Rotteveel Nov 25 '20 at 11:08
  • @MarkRotteveel i had mixed feelings on relevancy myself, so i did not include those in my answer. But at least it was something added, some reason d'etre if questionable, yet now it just turned into a later and shorter dup. – Arioch 'The Nov 25 '20 at 11:10