Extracting dimensions with REGEX-CodePudding

I have a table of warehouse parts, and one column has the description of each part. Within the description are the dimensions of the product, always in the format: HeightxWidthxDepth.

The description can be any number of characters long, so the dimensions aren't always in the same position of the string.

This is a sample of what the table looks like :

| Part No  | Description               |
| -------- | ------------------------- |
| ABCDEFGH | Cabinet 245 2070x1014x570 |
| AFFDETGL | Shelves 20104 870x300x330 |

In my query, I'd like to select each of the dimensions, such that the table looks like this:

| Part No  | Height| Width | Depth |
| -------- | ----- | ----- | ----- |
| ABCDEFGH | 2070  | 1014  | 570   |
| AFFDETGL | 870   | 300   | 330   |

I've managed to use REGEX to extract the full dimensions from the column with the following query:

SELECT
PART_NO,
REGEXP_SUBSTR(DESCRIPTION, '(\d (\.\d |)\s?x\s?\d (\.\d |)(\s?x\s?\d*(\.?\d |))?)')
FROM Products

But I'm unsure how to split this into 3 separate columns without using something like INSTR.

Can anyone tell me what the best method would be for this?

CodePudding user response：

Use capturing groups around the digit patterns and then extract the individual capturing groups using the 6th argument of REGEXP_SUBSTR:

SELECT PART_NO,
       REGEXP_SUBSTR(
         DESCRIPTION,
         '(\d \.?\d*)\s*x\s*(\d \.?\d*)\s*x\s*(\d*\.?\d*)',
         1,
         1,
         NULL,
         1
       ) AS height,
       REGEXP_SUBSTR(
         DESCRIPTION,
         '(\d \.?\d*)\s*x\s*(\d \.?\d*)\s*x\s*(\d*\.?\d*)',
         1,
         1,
         NULL,
         2
       ) AS width,
       REGEXP_SUBSTR(
         DESCRIPTION,
         '(\d \.?\d*)\s*x\s*(\d \.?\d*)\s*x\s*(\d*\.?\d*)',
         1,
         1,
         NULL,
         3
       ) AS depth
FROM   Products

Which, for the sample data:

CREATE TABLE products (Part_No, Description) AS
SELECT 'ABCDEFGH', 'Cabinet 245 2070x1014x570' FROM DUAL UNION ALL
SELECT 'AFFDETGL', 'Shelves 20104 870x300x330' FROM DUAL;

Outputs:

PART_NO HEIGHT WIDTH DEPTH

ABCDEFGH 2070 1014 570

AFFDETGL 870 300 330

PART_NO	HEIGHT	WIDTH	DEPTH
ABCDEFGH	2070	1014	570
AFFDETGL	870	300	330

db<>fiddle here