Condition to file some string from query

Hi experts,

I'm using SQL Developer to write query.  In one of my query I get pretty much the result I want but notice that there are some extra rows that I would like to exclude, i.e.,


For column 'Program_ID', I only want 'A0' amongst A0, A1, ... A9. I also want to have B, C, D and other values

Can you suggest what should I add in the WHERE statement to accomplish this task ?
Who is Participating?
sdstuberConnect With a Mentor Commented:
rather than an inclusion list, I think you're looking for an exclusion list

try these....

select * from (yourquery)
where not regexp_like(program_id,'A[1-5]')


select * from (yourquery)
where program_id not like '%A1%'
or program_id not like '%A2%'
or program_id not like '%A3%'
or program_id not like '%A4%'
or program_id not like '%A5%'


select * from (yourquery)
where program_id not like '%A%'
or (program_id like '%A0%')
slightwv (䄆 Netminder) Commented:
If you know what you want, an IN list:

where program_id in ('A0','A1','B','C','D')

add A2-A9.

If you mean something else, please clarify.
actually, I think you want...

where program_id in ('A0','B','C','D')

or,  if the goal is to pick up the first-A, which may or may not be A0 then try something like this...

select * from
(select t.*, row_number(partition by substr(program_id,1,1) order by to_number(substr(program_id,2))) rn from your_table t
where program_id in ('B','C','D')
or (program_id like 'A%')
) where program_id in ('B','C','D') or rn = 1
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Paul_ATLAuthor Commented:

I think your suggestion is close to what I'm looking for.  Also, I think I over simplify my question too much, let me elaborate for more clarity.

- In the result, Program_ID is not exactly string 'A0', 'A1', 'B', . . .
  But rather the 'A0', 'A1', . . . 'A9' is part of the string
  if the result is 'xxxxx - A0 yyyyy' then I want this included in the result
  if it has 'A1', . . . 'A9' then I don't want them

- The other part that I want, B, C, D, . . .
  The problem is that I do not have a finite list of what they are. I mean, it is a long list.
  As long as I does not contain 'A1', . . .'A9' then I want them.

I guess the easiest way is to say if 'A1', . . .'A9' is part of the string in Program_ID, then I do not want them.
I'm sorry that just confused me.  :)

could you post sample data and expected output?
I have a feeling that your example may be a little simplified and perhaps you could provide some concrete examples of the data and the expected output, but if all you just have a beginning letter that determines the group and only want the first member of that group, the following might work:
select program_id from
(select program_id, row_number() over (partition by substr(program_id,1,1) order by program_id) rn
where rn = 1;
it's that basically what I posted a half-hour ago?
Paul_ATLAuthor Commented:

Here is the sample data. Basically, I want to add a WHERE statement to exclude any result rows that PROGRAM_ID has 'A1' or 'A2', . . . 'A9'
you may want to include other parameters to narrow down the A*  strings.

Otherwise other A characters found in the string might create false matches.  if A will only show up when followed by 0-5 then the above should work fine

One final note,  middle-string matching is among the least efficient searching you can do, so hopefully the main query driving this performs well because the searching on the results isn't something you'll be able to index easily
Paul_ATLAuthor Commented:

running it now, will let you know later this eveining (it's a large query with up to 1.2M rows in result).
>>it's that basically what I posted a half-hour ago?<<
Not exactly. As I said I think the example may be simplistic. What if there is also an E0, E1, ...?
I suspect the results would only be the E0 record.
the idea was the same,  number each group so you can find the earliest one.
The part that is different is only the A numbers matter,  B,C,D  do not.  That's why I only use the rn check for the A's.  If there did exist an E group, we have no rules from the question to determine if they should be included at all or filtered, or if filtered in what way.

but,  it doesn't really matter now, with more feedback since those posts it's fairly apparent that numbering is probably not the way to go here.
Paul_ATLAuthor Commented:
need to make some tweaking but take the principle from this suggestion.
why the  B?

penalty grades shouldn't be given unless you requested additional information that was not provided.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.