Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    28

    Unanswered: Filtering in a select DB2 query

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Use TRANSLATE() to get rid of blank space and digits; only alphabetic characters will be left.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  4. #4
    Join Date
    Apr 2011
    Posts
    28
    Thanks a lot Tonkuma,

    It worked for me

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •