How Select SUBSTRING in ORACLE

Question

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!

Is CONSECUTIVE_NUMBER length fix or varying?

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

2017-01-26 19:26:00 +00:00
Commented Jan 26, 2017 at 19:26 — David דודו Markovitz
– David דודו Markovitz, Commented Jan 26, 2017 at 19:26

Alex Poole · Accepted Answer · 2017-01-26 19:49:47Z

3

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.

edited Jan 26, 2017 at 19:49

user5683823

answered Jan 26, 2017 at 18:49

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user5683823 Over a year ago

I can't help myself... I would have just written the last paragraph and disregarded the rest. I'll go ahead and change it to boldface (please change it back if you disagree). :-)

Alex Poole Over a year ago

@mathguy - yeah, but then it would have been NAA *8-) I had it just as a comment to start with but could just about justify squeezing it into a broader answer...

Gurwinder Singh · Accepted Answer · 2017-01-26 18:49:14Z

2

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;

edited Jan 26, 2017 at 18:49

answered Jan 26, 2017 at 18:41

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Comments

David דודו Markovitz · Accepted Answer · 2017-01-26 19:46:11Z

2

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;

edited Jan 26, 2017 at 19:46

answered Jan 26, 2017 at 19:25

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

1 Comment

Matías W. Over a year ago

Thanks to everyone, all the answers work perfectly. @Dudu Markovitz, the consecutive number is fix (6). Thank's

David דודו Markovitz · Accepted Answer · 2017-01-26 19:47:18Z

1

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;

edited Jan 26, 2017 at 19:47

user5683823

answered Jan 26, 2017 at 19:42

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

2 Comments

David דודו Markovitz Over a year ago

In that case, it is really simple.

David דודו Markovitz Over a year ago

Have you checked it?

Collectives™ on Stack Overflow

How Select SUBSTRING in ORACLE

4 Answers 4

2 Comments

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related