CODEWITHSUNDEEP
sqloracle-databaseselectsubstring
Matías W.
Matías W.

Reputation: 350

How Select SUBSTRING in ORACLE

I need select a subtring to get the consecutive number from a table field. My table is:

ORDER_NUM       ORDER_DATE   ORDER_TYPE  LOCATION   SALE_TYPE
10501702315618  08/01/17       43223       1050        18    
105017023186230 21/01/17       43221       1050        230

The field ORDER_NUM is generated as follows

[LOCATION] + [YY] + [CONSECUTIVE_NUMBER] + [SALE_TYPE]

The length of the fields LOCATION and SALE_TYPE Can be different.So, my query is:

SELECT 
SUBSTR(ORDER_NUM,LENGTH (ORDER_TYPE)  + 3,LENGTH (ORDER_NUM)   - LENGTH ( SALE_TYPE ) ),
ORDER_DATE
FROM 
CAT_ORDERS
WHERE 
LOCATION = '1050'
AND SALE_TYPE = '18'

The result is 

    SELECT SUBSTR('10501702315618',7,12) from dual
    RESULT: 02315618

Where the index position is:

 12345678901234
 10501702315618

How i can delete the SALE_TYPE characters from the string? There is another function for this ?

Thank's!

Upvotes: 1

Views: 753

Answers (4)

Alex Poole
Alex Poole

Reputation: 191245

Your description refers to the location, so it's odd that your offset is based on order type. The length of the substring needs to exclude the length of the location and the year as well:

SUBSTR(ORDER_NUM, LENGTH (LOCATION) + 3,
  LENGTH (ORDER_NUM) - LENGTH(LOCATION) - 2 - LENGTH (SALE_TYPE))

At the moment you're getting 12 characters from your offset, when you only want six, and there are only actually eight available.

Demo with your data:

WITH CAT_ORDERS (ORDER_NUN, ORDER_DATE, ORDER_TYPE, LOCATION, SALE_TYPE) AS (
  SELECT 10501702315618, TO_DATE('08/01/17', 'DD/MM/RR'), 43223, 1050, 18 FROM DUAL
  UNION ALL SELECT 105017023186230, TO_DATE('21/01/17', 'DD/MM/RR'), 43221, 1050, 230 FROM DUAL
)
SELECT SUBSTR(ORDER_NUM, LENGTH (LOCATION) + 3,
  LENGTH (ORDER_NUM) - LENGTH(LOCATION) - 2 - LENGTH (SALE_TYPE))
FROM CAT_ORDERS;

023156
023186

Your order number duplicates data from other columns, which doesn't seem ideal. It would be simpler to only store the 'consecutive number' part instead, and generate the full order number as a virtual column.

Upvotes: 3

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

Since we know now that the length of consecutive_number is fixed (namely, 6):

select  substr(ORDER_NUM,length(LOCATION)+3,6)
from    mytable;

or

select  substr(ORDER_NUM,-length(SALE_TYPE)-6,6)
from    mytable;

Upvotes: 1

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

select  regexp_substr(ORDER_NUM,'^' || LOCATION || '..(.*)' || SALE_TYPE || '$',1,1,'',1)
from    mytable;

Since we know now that the length of consecutive_number is fixed (namely, 6):

select  regexp_substr(ORDER_NUM,'^' || LOCATION || '..(.{6})',1,1,'',1)
from    mytable;

or

select  regexp_substr(ORDER_NUM,'(.{6})' || SALE_TYPE || '$',1,1,'',1)
from    mytable;

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

This should remove the sale_type characters from the end:

select 
  substr(substr(order_num, 0, length(order_num)-length(sale_type)), length(location) + 3)
from t;

Upvotes: 2

Related Questions