CODEWITHSUNDEEP
sqloracle-database
Ben Deacon
Ben Deacon

Reputation: 1

Extracting a year from a string in a column in Oracle SQL

I have a column titled SALE_PERIOD, which stores a string relating to when and where a product is sold. The column is filled using the following conventions:

  1. Two letters, which represents the month the sale was made
  2. The year the sale was made
  3. The number of the store where the sale occurred

For example, a product sold in JAN 2018 at store number 5 would be stored as "JA20185"

I need a query which will extract the year the sale was made from this column and allow me to write it into a new column in the following way:

SELECT SALE_PERIOD, (The code used to solve the problem) AS SALE_YEAR
FROM My_Table

I am aware the final code may need to look slightly different and any alternative solutions are also greatly appreciated.

Upvotes: 0

Views: 70

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You can split through [^[:digit:]] posix pattern by using regexp_substr() and regexp_replace() functions' combination :

with my_table( sale_period ) as
(
 select 'a product sold in JAN 2018 at store number 5' from dual
)
select substr(substr(trim(regexp_substr(sale_period,'[^[:digit:]]+')),-3),1,2) 
       ||regexp_replace(sale_period,'[^[:digit:]]') as sale_year
  from my_table

Demo

Upvotes: 0

MT0
MT0

Reputation: 167784

Use SUBSTR to get the 4-character year substring starting from the 3rd character and then convert it to a number:

SELECT sale_period,
       TO_NUMBER( SUBSTR( sale_period, 3, 4 ) ) AS sale_year,
       -- and for the other components:
       SUBSTR( sale_period, 1, 2 ) AS sale_month,
       TO_NUMBER( SUBSTR( sale_period, 7 ) ) AS sale_store
FROM   my_table;

outputs:

SALE_PERIOD | SALE_YEAR | SALE_MONTH | SALE_STORE
:---------- | --------: | :--------- | ---------:
JA20185     |      2018 | JA         |          5
DE2019123   |      2019 | DE         |        123

Since the SALE_PERIOD column has a well specified format for the sub-strings you could also add virtual columns to the table:

ALTER TABLE my_table ADD (
  sale_year  NUMBER(4,0) GENERATED ALWAYS AS ( TO_NUMBER( SUBSTR( sale_period, 3, 4 ) ) ) VIRTUAL,
  sale_month CHAR(2)     GENERATED ALWAYS AS ( CAST( SUBSTR( sale_period, 1, 2 ) AS CHAR(2) ) ) VIRTUAL,
  sale_store NUMBER(5,0) GENERATED ALWAYS AS ( TO_NUMBER( SUBSTR( sale_period, 7 ) ) ) VIRTUAL
)

then:

SELECT * FROM my_table;

gives the same output as above with those additional virtual columns.

db<>fiddle here

Upvotes: 3

Related Questions