I work in a factory.
We manufacture Batches. The Batches are referred to by Numbers like 1000585855; with the last 6 Digits being unique to each Batch.
The first stage of manufacturing involves two Batches being processed together.
Generally, the Batches only differ by the last digit being +1 larger. So for Batch 1000585855, the 'sister lot' will be 1000585856. (When we reference these Lots being processed in the Database, they are quoted as 1000585855_000585856.)
Occasionally however, the sister lot may be +2 or +3 larger (really it can be + any natural number). So for example, I have seen 1000456872_0000456874.
The information relating to the batches is stored in an Oracle Database Table. The Database Table is structured like so:
| BATCH_ID |
|---|
| 1000731655_000731656 |
| 1000746832_000746833 |
| 1000731637_000731639 |
| 1000575235_000575236 |
Is it possible to construct a Query, whereby I obtain the BATCH_IDs, only where the difference between the 10th and 20th Characters (Bolded) is greater than 1?
So, if I was to utilise such a Query in the above example table, the only BATCH_ID returned should be 1000731637_9. (As 9 minus 7 = 2)
WITH -- S a m p l e D a t a :
tbl (BATCH_ID) AS
(
Select '1000731655_000731656' From Dual Union All
Select '1000746832_000746833' From Dual Union All
Select '1000731637_000731639' From Dual Union All
Select '1000575235_000575236' From Dual
)
-- M a i n S Q L :
SELECT BATCH_ID
FROM tbl
WHERE To_Number(SubStr(BATCH_ID, 20, 1)) - To_Number(SubStr(BATCH_ID, 10, 1)) > 1
--
-- R e s u l t :
-- BATCH_ID
-- --------------------
-- 1000731637_000731639
Maybe it would be better to take two chars like 9th and 10th with 19th and 20th as there could be some problems with numbers ending with 0. Used To_number() because I dont like to be dependant on implicite conversions.
WHERE To_Number(SubStr(BATCH_ID, 19, 2)) - To_Number(SubStr(BATCH_ID, 9, 2)) > 1