string surch problum

Analytics
Enthusiast

string surch problum

hi
i have a requirement like as
column1
---------
ADJEIW
adfrrt
12i#()
e fttg
213233

and i want to select all the rows which are not having A to Z any where in the attribute, spaces special char numbers can come
i am using substr and retriving each char and checking with a NOT IN ('A','B','C',.....,'Z') condation

is there any short cut to do
3 REPLIES
Senior Apprentice

Re: string surch problum

Hi Rahul,
you could use
where col not like any ('%A%','%B%','%C%',...),
but this is quite ugly, too.

If there's the Oracle Translate function installed on your system, it's easy:
compare the string with the length of the string when A to Z characters are removed:

where str = otranslate(str, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', '')

Dieter
Enthusiast

Re: string surch problum

Rahul,

Maybe the following helps you to accomplish what you are looking for:

create table test (id char(20));

insert test values ('ADJEIW');
insert test values ('adfrrt');
insert test values ('12i#()');
insert test values ('e fttg');
insert test values ('213233');

SELECT
CASE WHEN SUBSTRING(id FROM 1 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9')
AND SUBSTRING(id FROM 2 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9')
AND SUBSTRING(id FROM 3 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9')
AND SUBSTRING(id FROM 4 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9')
AND SUBSTRING(id FROM 5 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9')
AND SUBSTRING(id FROM 6 FOR 1) IN ('0','1','2','3','4','5','6','7','8','9')
THEN id
END as column1
FROM test
WHERE column1 IS NOT NULL;

Good luck.
Enthusiast

Re: string surch problum

thank u ppl a lot