|
|
|
Junior Member
 
Group: Forum Members
Last Login: 10/21/2008 12:23:18 PM
Posts: 13,
Visits: 53
|
|
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
Regards, Rahul Win Win
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Today @ 4:54:41 PM
Posts: 534,
Visits: 285
|
|
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
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 11/27/2008 9:25:22 AM
Posts: 43,
Visits: 51
|
|
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.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 10/21/2008 12:23:18 PM
Posts: 13,
Visits: 53
|
|
thank u ppl a lot
Regards, Rahul Win Win
|
|
|
|