Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    7

    Unanswered: select only records containing alphabets

    Hi all, I am new to DB2 and am looking for some inspiration on this forum.

    I am having a bit of a problem with an issue and hope that the kind souls here can help me with it.

    I needed to select records that contain only alphabets. For example,
    Column A
    X13568J
    16879
    KT66980
    ALPHA
    3688908I
    235IKL86
    RICHTEXT

    The query should only extract 'ALPHA' and 'RICHTEXT'.

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Something like
    Code:
    ... where translate(columna, '', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') = ''
    Don't expect this to be fast.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can use the TRANSLATE function to translate all letters to an empty string and then filter out all rows where the result of the TRANSLATE on the whole string is not empty.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jul 2009
    Posts
    150

    Lightbulb small correction

    Quote Originally Posted by n_i View Post
    Something like
    Code:
    ... where translate(columna, '', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') = ''
    Don't expect this to be fast.
    With small correction, if you want:

    Code:
    ... 
    where 
    columna = replace(columna, ' ', '?') 
    and
    translate(columna, ' ', 
    'ABCDEFGHIJKLMNOPQRSTUVWXYZ' 
    || Lcase('ABCDEFGHIJKLMNOPQRSTUVWXYZ') ) = ' '
    ...

    Kara
    Last edited by DB2Plus; 12-01-09 at 22:58.

  5. #5
    Join Date
    Dec 2009
    Posts
    7
    Thanks to all for the quick response. It works wonderfully though it takes quite a while.

  6. #6
    Join Date
    Jul 2009
    Posts
    150

    Lightbulb Faster query

    Quote Originally Posted by thomast View Post
    Thanks to all for the quick response. It works wonderfully though it takes quite a while.
    Following query will work much faster:

    Code:
    select tt.columna
    from
    (select columna, nullif(columna, replace(columna, ' ', '?')) NRcolumna,
    translate(columna, ' ', 
    'ABCDEFGHIJKLMNOPQRSTUVWXYZ' 
    || Lcase('ABCDEFGHIJKLMNOPQRSTUVWXYZ') ) Tcolumna
    from your_table  ) tt
    where NRcolumna is null and Tcolumna = ' '
    Kara

  7. #7
    Join Date
    Dec 2009
    Posts
    7
    Thanks Kara. I removed the LCASE function as i did not need it.
    With your new stt, I realise that it is not able to extract any record. Probably due to 'is null' even though the nested select stt works fine.

Posting Permissions

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