[SOLVED] Extract 12 character from a string impala query

Extract 12 character from a string impala query

I have a column that contains productID,catalogID and organization. For example 1000002121US200100 contains 1000002121=product ID, US20=catalogID, 01=product channel and finally 00=sales area.

Is there a way I can split this up using the position of the string. I can do a left('fieldname',10) to get the first 10 characters which gives me the product ID. How do I extract next 4 characters into a separate column and so on?

I have tried using left() function, split_part and regex_extract

Solution

Wouldn't that work?

-- To get the productID (first 10 characters)
SELECT LEFT(fieldname, 10) AS productID,

-- To get the catalogID (next 4 characters)
SUBSTRING(fieldname, 11, 4) AS catalogID,

-- To get the product channel (next 2 characters)
SUBSTRING(fieldname, 15, 2) AS productChannel,

-- To get the sales area (last 2 characters)
SUBSTRING(fieldname, 17, 2) AS salesArea

FROM your_table;