select only records containing alphabets

thomast · #1 (**permalink**) 12-01-09, 20:30

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.

n_i · #2 (**permalink**) 12-01-09, 20:35

Something like

Code:

... where translate(columna, '', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') = ''

Don't expect this to be fast.

stolze · #3 (**permalink**) 12-01-09, 20:37

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.

DB2Plus · #4 (**permalink**) 12-01-09, 21:47

Quote:

Originally Posted by n_i

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

thomast · #5 (**permalink**) 12-01-09, 22:03

Thanks to all for the quick response. It works wonderfully though it takes quite a while.

DB2Plus · #6 (**permalink**) 12-01-09, 23:19

Quote:

Originally Posted by thomast

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