CODEWITHSUNDEEP
sqldb2
Frantumn
Frantumn

Reputation: 1764

determine DB2 text string length

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)

Upvotes: 21

Views: 139242

Answers (3)

Robert Lujo
Robert Lujo

Reputation: 16361

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

Upvotes: 37

Sushim Amitabh
Sushim Amitabh

Reputation: 11

Mostly we write below statement select * from table where length(ltrim(rtrim(field)))=10;

Upvotes: 0

david a.
david a.

Reputation: 5291

This will grab records with strings (in the fieldName column) that are 10 characters long:

 select * from table where length(fieldName)=10

Upvotes: 29

Related Questions