All Forums Analytics
08 Nov 2007
string surch problum

hi i have a requirement like as column1---------ADJEIWadfrrt12i#()e fttg213233and i want to select all the rows which are not having A to Z any where in the attribute, spaces special char numbers can comei 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

dnoeth 4628 posts Joined 11/04
08 Nov 2007

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

Dieter

famalau 43 posts Joined 08/07
09 Nov 2007

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');SELECTCASE 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 column1FROM testWHERE column1 IS NOT NULL;Good luck.

Regards,

Fabio

09 Nov 2007

thank u ppl a lot

You must sign in to leave a comment.