Filtering in a select DB2 query

ashu000 · #1 (**permalink**) 06-03-11, 07:30

Hi All,

One of my table is having one column which is a CHAR(4) field. This column is having characters from one to 4 characters and some numbers like 98, 99.

I have to take out only those values which are of 2 characters only, filtering out all others. In this case, the numbers must not come, but they are of 2 digit and are characters, so these need to be filter out in my query.

Kindly suggest what I should write in a query to get the required result.

Thanks a lot.

n_i · #2 (**permalink**) 06-03-11, 08:10

Use TRANSLATE() to get rid of blank space and digits; only alphabetic characters will be left.

tonkuma · #3 (**permalink**) 06-03-11, 09:09

All examples are not tested.

Assumptions:
1) Characters are left justified.
For example, ' AA ' or 'A B ' are not in the column.

2-1) There is a possibility that one of first two characters is numeric and another one is not numeric.
For example, 'A7 ' or '4V '
2-2) Include(not filter out) those values.

Example 1:

Code:

 WHERE SUBSTR(char4 , 3 , 2) =  ''
   AND SUBSTR(char4 , 2 , 1) <> ''
   AND
   (   SUBSTR(char4 , 1 , 1) NOT BETWEEN '0' AND '9'
    OR SUBSTR(char4 , 2 , 1) NOT BETWEEN '0' AND '9'
   )

Example 2:

Code:

 WHERE LENGTH( RTRIM(char4) ) = 2
   AND
   (   SUBSTR(char4 , 1 , 1) NOT BETWEEN '0' AND '9'
    OR SUBSTR(char4 , 2 , 1) NOT BETWEEN '0' AND '9'
   )

Example 3:

Code:

 WHERE LENGTH( RTRIM(char4) ) = 2
   AND TRANSLATE(char4 , '' , '0123456789' , ' ') <> ''

Example 4:

Code:

 WHERE LENGTH( RTRIM(char4) ) = 2
   AND LOCATE( SUBSTR(char4 , 1 , 2)
             , '00102030405060708091121314151617181922324252627282933435363738394454647484955657585966768697787988990'
             ) = 0

If assumption 2) was not true
(i.e. if one character was a digit then all other characters must be digits),
then
LENGTH( RTRIM( TRANSLATE(char4 , '' , '0123456789' , ' ') ) ) = 2

ashu000 · #4 (**permalink**) 06-04-11, 05:11

Thanks a lot Tonkuma,

It worked for me

Internet Services

Web Development

Digital Marketing

Consumer Tech