0

I have a dimension field that holds data in the below format. I am using teradata to query this field.

  1. 10 x 10 x 10
  2. 5.0x6x7
  3. 10 x 12x 1
  4. 6.0 x6.0 x6.0
  5. 0 X 0 X 0

I was wondering how should I go about parsing this filed to only obtain the numbers into 3 different columns.

Tamzid Hossain
  • 1
  • 1
  • Please supply further detail and the relevant code that you already have – Sam Walpole Jun 18 '20 at 11:37
  • I am currently using the following code but it doesn't capture everything entirely. Especially if there is a ".0" at the end of the code. cast(TRIM(StrTok(dimensions, UPPER (' x'),1))as decimal (18,0)) as prod_length, cast(TRIM(StrTok(dimensions, UPPER (' x'),3)) as decimal (18,0)) as prod_width, cast(TRIM(StrTok(dimensions, UPPER (' x'),5)) as decimal (18,0)) as prod_height – Tamzid Hossain Jun 18 '20 at 22:31
  • I have also tried using this other code but it doesn't capture the last column well either dimensions, cast(regexp_substr(dimensions, '[0-9]+') as int) as l, cast(substr(regexp_substr(dimensions, 'x +[0-9]+'), 3, 10) as int) as w, cast(substr(regexp_substr(dimensions, 'x +[0-9]+'), 5, 10) as int) as h – Tamzid Hossain Jun 18 '20 at 22:35

1 Answers1

0

Something like this should work or at least get you close. 

REGEXP_SUBSTR(DATA, '(.*?)(x ?|$)', 1, 1, 'i', 1) AS length,
REGEXP_SUBSTR(DATA, '(.*?)(x ?|$)', 1, 2, 'i', 1) AS width,
REGEXP_SUBSTR(DATA, '(.*?)(x ?|$)', 1, 3, 'i', 1) AS height

Return the first captured group of a set of characters that are followed by a case-insensitive 'x' and an optional space or the end of the line. The 4th argument is the instance of this match to return.

Gary_W
  • 9,933
  • 1
  • 22
  • 40