PL/SQL Getting a list of sequence values

To clarify the requirement:
Return any values in sequence before and after some starting value until a gap is reached.

You'd have to define the 'gap' by stripping part of what the user enters and then use that part as a wildcard. For example: user enters A1021 - strip last 2 characters - you get A10 - then select records like A10%

Would this work for you? This assuming Data is a table that contains your records.

here's a little more compact and efficient version of each...



SELECT data
  FROM (SELECT data,
               LAST_VALUE(
                   CASE
                       WHEN TO_NUMBER(REGEXP_SUBSTR(data, '[0-9]+')) !=
                                TO_NUMBER(REGEXP_SUBSTR(prevdata, '[0-9]+')) + 1
                       THEN
                           data
                   END IGNORE NULLS
               )
               OVER (ORDER BY data)
                   seq_first
          FROM (SELECT data, LAG(data) OVER (ORDER BY data) prevdata FROM yourtable))
 WHERE seq_first = 'A1001'



SELECT data
  FROM (SELECT data, COLLECT(data) OVER (PARTITION BY seq_first) x
          FROM (SELECT data,
                       LAST_VALUE(
                           CASE
                               WHEN TO_NUMBER(REGEXP_SUBSTR(data, '[0-9]+')) !=
                                        TO_NUMBER(REGEXP_SUBSTR(prevdata, '[0-9]+')) + 1
                               THEN
                                   data
                           END IGNORE NULLS
                       )
                       OVER (ORDER BY data)
                           seq_first
                  FROM (SELECT data,                              
                               LAG(data) OVER (ORDER BY data) prevdata
                          FROM yourtable)
                           ))
 WHERE 'A1002' MEMBER OF CAST(x AS ora_mining_varchar2_nt)



note, to find sequences I'm only looking at the numeric portion of the data.

If you might have

A0001
B0001
A0002
B0002
A0004


that would be a problem, it's solvable, but the code above doesn't do that.
Also I assume the sequence values are unique.

If your data values have some other pattern please elaborate and I'll adjust the queries accordingly.

Yes, slightwv, it goes until a gap is reached.  It could be 1 or a thousand in between.

You asked "for a way to get a list of data based on what a user enters".  You didn't indicate whether the efficiency of the search (or response time for the user) is important or not.  Whenever you ask Oracle to evaluate just a portion of a column value, especially if that portion does not include the first character of the string, you run the risk of forcing a (slow) full-table scan.  Of course, your data volumes in this table are a big factor.  If this table is small, this may not be a problem for you.

You also suggested from your question title that this will be used in PL\SQL, but you did not indicate what PL\SQL should do with the list of records retrieved.  Will these valus just be used inside a PL\SQL block or procedure, or will they actually be returned to the user (or to a calling program)?

What is the business problem you are trying to solve here?  (There may be a better, simpler or more-efficient option.)  Do you have any control over the application code that creates these records?

>>Yes, slightwv, it goes until a gap is reached.  It could be 1 or a thousand in between.

Start with what sdstuber posted in http:#a39449085 and go from there.

the only thing I know of it that the field could be 20 characters long.

So you could have a something like:

A1001
A1002
A1003
A1000004
B1004

The sequence should stop at the A1003

markgeer,

the values will be returned to the user

A1001
A1002
A1003
A1000004
B1004

As long as the format is always a single letter followed by digits and the values are unique, then what I posted in http:#a39449085  should work.

If it is always one letter followed by digits, it will be more efficient to replace

REGEXP_SUBSTR(data, '[0-9]+')

with

SUBSTR(data,2)

but otherwise both of the queries should work fine

If I wasn't clear enough, please let me know.  I am doing a number of thing at the same time and I hurried to write the post.

Just try the queries I posted.

If they don't work - please provide sample data that shows the error along with expected results.  

You don't need to post the wrong results.

Please Delete question