CODEWITHSUNDEEP
sqlstringoracle-databasesql-updatesubstr
Eda
Eda

Reputation: 93

How to retrieve the last part (i.e. a number varies in length) of a text column of which's length varies in Oracle and store this in another column?

I am trying to get the last number from a Oracle SQL column called Description and store it in another column called Thickness.

Oracle Frames Table

As you see, the length of the description varies as well as the length of the number at the very end. It is sometimes a double such as 1.5, sometimes an integer like 3. I have tried checking for 'X' at the end but it was not a success since RIGHT keyword is not valid in Oracle.

Thanks in advance for your help!

Upvotes: 0

Views: 27

Answers (3)

MT0
MT0

Reputation: 168416

Use INSTR with the position as -1 to start looking from the right of the string:

(I've included width and height as well as thickness.)

UPDATE table_name
SET width     = TO_NUMBER(
                  SUBSTR(
                    description,
                    INSTR( description, ' ', -1 ) + 1,
                    INSTR( description, 'X', -1, 2 ) - INSTR( description, ' ', -1 ) - 1
                  )
                ),
    height    = TO_NUMBER(
                  SUBSTR(
                    description,
                    INSTR( description, 'X', -1, 2 ) + 1,
                    INSTR( description, 'X', -1, 1 ) - INSTR( description, 'X', -1, 2 ) - 1
                  )
                ),
    thickness = TO_NUMBER(
                  SUBSTR(
                    description,
                    INSTR( description, 'X', -1 ) + 1
                  )
                );

Which, for the sample data:

CREATE TABLE table_name ( description, width, height, thickness ) AS
SELECT 'RAAM NIB 319X319X1.5', 0, 0, 0 FROM DUAL UNION ALL
SELECT 'RAAM ZW 327X307X2', 0, 0, 0 FROM DUAL;

Changes the value to:

DESCRIPTION          | WIDTH | HEIGHT | THICKNESS
:------------------- | ----: | -----: | --------:
RAAM NIB 319X319X1.5 |   319 |    319 |       1.5
RAAM ZW 327X307X2    |   327 |    307 |         2

db<>fiddle here

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65373

You can use REGEXP_SUBSTR() function containing the pattern [:alpha:] as

SELECT REGEXP_SUBSTR(description,'[^[:alpha:]]+$') AS thickness
  FROM t

I think, no need to store a computable value within a column in terms of data structural design. But if you prefer, a simple CTAS statement such as below one would help

CREATE TABLE t AS
WITH t(description) AS
(
 SELECT 'RAAM NIB 319X319X1.5'    FROM dual UNION ALL
 SELECT 'RAAM ZW 327X307X2'       FROM dual UNION ALL
 SELECT 'RAAM ZW 460.5X430.5X2.5' FROM dual 
)
SELECT description, REGEXP_SUBSTR(description,'[^[:alpha:]]+$') AS thickness
  FROM t

Demo

Upvotes: 1

GMB
GMB

Reputation: 222622

One option uses regexp_substr():

update mytable 
set thickness = regexp_substr(description, '\d+(\.\d+)?$')

Note that you could very well use a computed column rather than actually storing this derived information:

alter table mytable drop column thickness;

alter table mytable
add thickness as (to_number(regexp_substr(description, '\d+(\.\d+)?$')));

Demo on DB Fiddle

Upvotes: 2

Related Questions