Why does STRLEN return the same value for all rows in my table?

You probably have a char column. Character type is padded with spaces. Consider the following example:

c1 char(5),
v1 varchar(5)
INSERT INTO TESTLEN values ('abcd', 'abcd')
select strlen(c1), strlen(v1) from TESTLEN;

Returns 5 for char column c1 and 4 for varchar column v1. This is because c1 actually contains 'abcd ', and not 'abcd'. See FAQ #237 for more details.

If you want to get the length of the data (without trailing spaces), you can trim the char column using TRIM or RTRIM function, like this:

select strlen(trim(c1)) from TESTLEN;

Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.

If you are a commercial tool maker and your tool features a great way to handle the issue written about in this FAQ, please check out our advertisement page.

All contents are copyright © 2007-2017 FirebirdFAQ.org unless otherwise stated in the text.

Links   Firebird   News   FlameRobin   Home Inventory powered by FB  
Add content   Advertise   About  

 Installation and setup
 Backup and restore
 Connectivity and API
 Errors and error codes