Results 1 to 5 of 5

Thread: SQL Query

  1. #1
    Join Date
    Apr 2015
    Posts
    2

    Question Unanswered: SQL Query

    My requirement is
    select * from table a where ID like 'delr' || trim(substr(busns_asct_id,5)) || '%';
    based on the above quey
    got records like "delr112591-8218-1","delr112659-8323-1" but in that i need to eliminate ID like "delr60201-T","delr99999-A" and "delr1 -5"

    Please help on this

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    what are the riles for the data you want to exclude. If it is litterly the 3 listed then do

    Code:
    select * from table a 
    where ID like  'delr' || trim(substr(busns_asct_id,5)) || '%'
    and ID not in ('delr60201-T','delr99999-A', 'delr1    -5');
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Apr 2015
    Posts
    2

    Question Sql query

    Thanks. Based on my Query
    select * from table A where ID like 'delr' || trim(substr(busns_asct_id,5)) || '%';
    got 145 in that 21 records are like delr60201-T,delr99999-A and one record is in delr1 -5. I need to fetch only 125 data like delr112591-8218-1 etc. Need to eliminate the 22 records which is in the format mentioned above.

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    "I need to fetch only 125 data like delr112591-8218-1 etc"

    delr60201-T is like 'delr112591-8218-1 etc' - you'll need to be more precise in which records you are trying to eliminate i.e. what differentiates all of them from the ones that you are trying to keep. Why is delr112591-8218-1 OK to keep but not delr60201-T?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    According to what's been said so far, something like this might do the job:

    Code:
    select * 
    from table a 
    where ID like  'delr' || trim(substr(busns_asct_id,5)) || '%'
      and regexp_count(ID, '-') > 1;

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •