determine DB2 text string length

Question

I am trying to find out how to write an SQL statement that will grab fields where the string is not 12 characters long  I only want to grab the string if they are 10 characters    What function can do this in DB2   I figured it would be something like this  but I can t find anything on it  select   from table where not length fieldName  12

User · Answer

From similar question DB2 - find and compare the lentgh of the value in a table field - add RTRIM since LENGTH will return length of column definition. This should be correct:

select * from table where length(RTRIM(fieldName))=10

UPDATE 27.5.2019: maybe on older db2 versions the LENGTH function returned the length of column definition. On db2 10.5 I have tried the function and it returns data length, not column definition length:

select fieldname
, length(fieldName) len_only
, length(RTRIM(fieldName)) len_rtrim
from (values (cast('1234567890  ' as varchar(30)) )) 
as tab(fieldName)

FIELDNAME                      LEN_ONLY    LEN_RTRIM
------------------------------ ----------- -----------
1234567890                              12          10

One can test this by using this term:

where length(fieldName)!=length(rtrim(fieldName))

User · Answer

Mostly we write below statement select   from table where length ltrim rtrim field    10

User · Answer

This will grab records with strings  in the fieldName column  that are 10 characters long    select   from table where length fieldName  10

[sql] determine DB2 text string length

Examples related to sql

Examples related to db2