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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

I'm sorry that just confused me.  :)

could you post sample data and expected output?
awking00Information Technology SpecialistCommented:
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'
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%')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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).
awking00Information Technology SpecialistCommented:
>>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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.