0

I have an long string with the syntax

'item_name (price + tax), item_name (price + tax), item_name (price +tax)'

that I want to get into the rows: item_1, price_1, item_2, price_2, etc.

I used the below methods to get item_1 and item_2 but I can't seem to pull the prices out because I get an error with REGEXP_SUBSTR(items,'([^-]+',1,1) due to the "(". What am I doing wrong?

,REGEXP_SUBSTR(items,'[^-]+',1,1) as item_1 ,SUBSTRING( REGEXP_SUBSTR(items,',[^-]+',1,1) ,2 ,LEN(REGEXP_SUBSTR(items,',[^-]+',1,1))-2 ) as item_2

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
Tyler Spitz
  • 1
  • 1

2 Answers2

0

Use split_to_table() and then some addition string manipulation:

select split_part(t.value, ' ', 1) as item_name,
       regexp_substr(items, '[(]([^ +]+)', 1, 1, 'e') as price
from t cross join lateral
     table( split_to_table(t.items, ',') ) as t;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Unfortunately, this didn't work because I had some items with "(" in the name (i.e. something like "(2lbs)"). My solution above seemed to get it right. – Tyler Spitz Nov 16 '20 at 19:36
0

[EDIT] I added the replace function and was able to get all the numbers right this time

replace( SUBSTRING( REGEXP_SUBSTR(items,'[^+]+',1,1) ,-6 ,5 ),'(') as price_1

I actually was able to figure out most of this by using the below syntax. Whenever a row had only one item (i.e. item_1, price_1) and nothing more, the other columns just show blank. For numbers that are like "18.99" vs. "9.99" though, I still need to figure this out how to pull those both correctly.

,REGEXP_SUBSTR(items,'[^-]+',1,1) as item_1 ,SUBSTRING( REGEXP_SUBSTR(items,'[^+]+',1,1) ,-5 ,5 ) as price_1

,SUBSTRING(
    REGEXP_SUBSTR(items,',[^-]+',1,1)
    ,2
    ,LEN(REGEXP_SUBSTR(items,',[^-]+',1,1))-2
        ) as item_2
,SUBSTRING(
    REGEXP_SUBSTR(items,'[^+]+',1,2)
    ,-5
    ,5
        ) as price_2
        
,SUBSTRING(
    REGEXP_SUBSTR(items,',[^-]+',1,2)
    ,2
    ,LEN(REGEXP_SUBSTR(items,',[^-]+',1,2))-2
        ) as item_3
,SUBSTRING(
    REGEXP_SUBSTR(items,'[^+]+',1,3)
    ,-5
    ,5
        ) as price_3
Tyler Spitz
  • 1
  • 1