CODEWITHSUNDEEP
sqldb2
zod
zod

Reputation: 12437

DB2 - find and compare the lentgh of the value in a table field

I am trying to find and compare the length of a field value in table.

Table1
Id Name 
1  abcd
2  xyz
3  Y

I am trying to find the name with length more than 3. The result should have only record with id 1

I tried LEN. Its not in db2 . i tried length . Its giving the field length ,not the actaul length of the value in that field.

select id,name,LENGTH(name) as namelength  from table1 
group by id,name having LENGTH(name)>3

Upvotes: 1

Views: 17554

Answers (2)

bhamby
bhamby

Reputation: 15469

Your field is probably defined as CHAR, which pads the remaining length with spaces. You can TRIM this off, and you'll get what you're looking for:

SELECT id,name,LENGTH(TRIM(name)) AS namelength
FROM table1 
GROUP BY id,name
HAVING LENGTH(TRIM(name)) > 3

This assumes Linux/Unix/Windows DB2. If you're on the Mainframe z/OS DB2, then you'll use RTRIM instead of TRIM.

Upvotes: 4

premnathcs
premnathcs

Reputation: 555

You can check the condition in where clause itself,

select id,name,LENGTH(name) as namelength from table1 where LENGTH(name)>3

Upvotes: 0

Related Questions