CODEWITHSUNDEEP
sqldb2
Sewder
Sewder

Reputation: 754

Return column with only number of characters

I have this query:

     SELECT GUITAR_NO,COUNT(INVENTORY)
     FROM INVENTORY.GUITARS
     WHERE guitars.guitar_type = 'FENDER' and GUITARS.Guitar_no LIKE('4%')
     GROUP BY GUITAR_NO

I'm trying to get the query to only return guitar characters that are 6 characters long, no longer no shorter. Is there a way to do this in DB2?

Bonus: How do I return only those guitar_no where Count(inventory) > 3

Upvotes: 0

Views: 59

Answers (2)

mjsqu
mjsqu

Reputation: 5452

  1. Use LENGTH(TRIM(guitar_type)) = 6 in your WHERE clause

  2. Use HAVING COUNT(INVENTORY) > 3 after your GROUP BY clause

Upvotes: 1

user275683
user275683

Reputation:

Just use LENGTH function that returns integer representation of what ever field you pass in.

In your WHERE clause just add AND LENGTH(GuitarName) = 6 just replace GuitarName with your field that you need to check.

to get rid of extra white space just wrap it in TRIM function

LENGTH(TRIM(GuitarName)) = 6

Bonus:

Just add HAVING Clause after GROUP BY

 SELECT GUITAR_NO,COUNT(INVENTORY)
 FROM INVENTORY.GUITARS
 WHERE guitars.guitar_type = 'FENDER' 
    and GUITARS.Guitar_no LIKE('4%') 
    and LENGTH(TRIM(guitars.guitar_type)) = 6 
 GROUP BY GUITAR_NO
 HAVING COUNT(INVENTORY) > 3

Upvotes: 1

Related Questions