Forum Stats

  • 3,740,484 Users
  • 2,248,263 Discussions
  • 7,861,274 Comments

Discussions

Combining multiple regexp_like statements & correct regex syntax

4169408
4169408 Member Posts: 2
edited Feb 3, 2020 12:36PM in SQL & PL/SQL

I’m working on creating various 0/1 product flags category variables based on multiple substrings within a string field. The string values contained in the category_description field don’t stick to a consistent classification pattern. Notice for each flag variable I’m using two regexp_like statements. The two things I’m trying to accomplish are.

  1. For each category flag variable combine the two regexp_like statements into only one regexp_like statement
  2. I’m still trying to correct some of the issues I’m running to with the regex syntax.
    • Swimsuit classifying categories which actually dress suits and not swimsuits or flagging jumpsuits as swimsuits
    • A category is being flagged as swimwear when it represents just shorts. Example: Men > Clothing > Shorts & Swimwear > Shorts
    • Situations where there are no spaces between substrings. Example: where the description Layette/Infant Unisex - Accessories – Sale is not being flagged as baby when it contains the word infant

Below is my current code, sample data, and my desired output. Any assistance will be greatly appreciated.  Thanks! 

Sample data:

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">CREATE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">TABLE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> category_flags<br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/>category_description VARCHAR</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">80</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">);</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">INSERT</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">INTO</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> category_flags <br/>   </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">VALUES</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> <br/><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Baby > Basics > Accessories > Hats,Scarves, & Gloves"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Men > Shoes & Accessories > Hats"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Men > Shoes & Accessories > Ties"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Women > Clothing > Jackets"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Women > Shoes & Accessories > Hats"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Kids > Boys > Jackets"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Tall/Men/Outerwear/Cloth/Sale"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Men > Bottoms > Swim"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Men > Bottoms > Swimwear"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"US/Big_Tall/Men/Chinos/Flat_Front"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"men>clothing>sweatshirts"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"men>clothing>swimwear"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Children/Girls/Outfit"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Men > Accessories > Fragrance"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Black_Top/Men/Dress_Shirts/Slim_Fit/Sale"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Men > Bottoms > Suits, Sport Coats & Trousers"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Women > Tops > Sweaters"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Kids > Accessories > Boy's Accessories > Bags"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"boys>accessories>socks"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Blue_Look/Women/Pants/Sale"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"women > Clothing/Knits/> Jackets"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Women > Clothing > Jackets"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Women > Shoes & Accessories > Hats"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Women > Clothing > Jackets"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Dresswear/Women > Belts"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"clothing > men >shorts & swim trunks > swimtrunks"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Men > Clothing > Shorts & Swimwear > Shorts"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"men>clothing>swimsuits"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"boys>accessories>socks"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Layette/Infant Unisex - Accessories"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Layette/Infant Unisex - Accessories - Sale"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Basics > Baby Boy & Girl> Accessories > Hats,Scarves, & Gloves"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Women/Clothing & Shoes/Swimwear"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Evening/Men > Shoes & Accessories > Ties"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Summer/Child/Jumpers"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"women/fall/clothing & accessories/jumpsuits"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">"Women/Clothing & Swimwear/Skirts"</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">);</span>

Current script:

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">select</span>

<span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">case</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">when</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> regexp_like</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'(.[^o]|[^w]o)men|^men'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">   <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">not</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> regexp_like</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'accessories|hats|bags|belts|shoes|socks|ties'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">then</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">else</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">0</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">end</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">as</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> Mens_Apparel</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> <br/><br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">case</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">when</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> regexp_like</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'(.[^o]|[^w]o)men|^men'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">   <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">  regexp_like</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'accessories|hats|bags|belts|shoes|socks|ties'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">then</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">else</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">0</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">end</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">as</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> Mens_Accessories</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/><br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">case</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">when</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> regexp_like </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'women'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">   <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">not</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> regexp_like</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'accessories|hats|bags|belts|shoes|socks|ties'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">then</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">else</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">0</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">end</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">as</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> Womens_Apprarel</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> <br/><br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">case</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">when</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> regexp_like </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'women'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">   <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">  regexp_like</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'accessories|hats|bags|belts|shoes|socks|ties'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">then</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">else</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">0</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">end</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">as</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> Womens_Accessories </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> <br/><br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">case</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">when</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> regexp_like </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'boys?|girls?|junior|child(ren)?|kids?'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">not</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">  regexp_like </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'infant|babys?|hidden|womens?|mens?|gift cards?|test'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">then</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">else</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">0</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">end</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">as</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> Kids_Products  </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/><br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">case</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">when</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> regexp_like</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'toddler|infant|babys?|'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">not</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">  regexp_like </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'hidden|womens|mens|^gift cards'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">then</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">else</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">0</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">end</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">as</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> Baby_Products</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/><br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">case</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">when</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">  regexp_like</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'(.[^o]|[^w]o)men|^men'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">  <br/>   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> regexp_like</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'swim(wear)?|swim(suit)?'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">then</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">else</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">0</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">end</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">as</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">  Mens_Swimwear  </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"><br/><br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">case</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">when</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">  regexp_like</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'womens'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> <br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">and</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> regexp_like</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">category_description</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'(swim(wear|suits))'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">,</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit;">'i'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">then</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">else</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit;">0</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">end</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">as</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;">  Womens_Swimwear  <br/><br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit;">from</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit;"> category_flags </span>

         

<td class="xl66" style="
category_descriptionMens_ApparelWomens_ApprarelMens_AccessoriesWomens_AccessoriesMens_SwimWomens_SwimKids_ProductsBaby_Products
Baby > Basics > Accessories > Hats,Scarves, & Gloves00000001
Men > Shoes & Accessories > Hats00100000
Men > Shoes & Accessories > Ties00100000
Women > Clothing > Jackets01000000
Women > Shoes & Accessories > Hats00010000
Kids > Boys > Jackets00000010
Tall/Men/Outerwear/Cloth/Sale10000000
Men > Bottoms > Swim10001000
Men > Bottoms > Swimwear10001000
US/Big_Tall/Men/Chinos/Flat_Front10000000
men>clothing>sweatshirts10000000
men>clothing>swimwear10001000
Children/Girls/Outfit00000010
Men > Accessories > Fragrance00100000
Black_Top/Men/Dress_Shirts/Slim_Fit/Sale10000
4169408

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,700 Red Diamond
    edited Feb 2, 2020 9:17PM

    Hi,

    Thanks for posting the sample data and the desired results.  Whenever you have a question, you also need to post an explanation (in general terms) of how you get the desired results from the given data.  Also say which version of Oracle you're using (e.g., 12.2.0.2.0). 

    See the Forum FAQ:

    It would probably be simpler to factor out the tests.  That is, decide if a row matches men, women, kinds or infants in one expression, and decide if that row matches apparel, accessories or swimwear in another, then use the results of those two tests to populate all the 0/1 columns.

    Is the sample data you posted really a good test?  Looking at your existing code, it seems that the presence or absence of some words, like 'hidden' can be important, but the sample data doesn't include any examples.

    It would help a lot if you formatted the output so horizontal scrolling wasn't necessary.  Instead of long column names like 'Womens_Accessories' (18 characters), why not use 'WA' (2 characters) when you post here?  Adapting the solution to use long names in your production code will be trivial.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,777 Bronze Crown
    edited Feb 2, 2020 9:48PM

    As well as what Frank said, post an insert that actually works in Oracle and one that does NOT contain duplicates...

    As far as the last case statement goes, you could re-write it something like

    case when regexp_like(category_description, 'women.+swim(wear|suits)', 'i') then 1 else 0 end as women_swimwear

    To test out your regex, you could use a website like this:

    https://regex101.com/

    ...and to read up on regex to get a better understanding, perhaps this:

    Regex Tutorial—From Regex 101 to Advanced Regex

    4169408
  • Paulzip
    Paulzip Member Posts: 8,369 Blue Diamond
    edited Feb 3, 2020 2:04AM

    Unless I'm misunderstanding you, why don't you just add those flags to the table, populate them with your current script and manually correct them? This'll be a one off process. Newly added items would be manually scripted with the flags as part of the insert statement.

    Trying to regex a robust permanent solution is only going to end up with issues and gotchas.

  • mathguy
    mathguy Member Posts: 9,836 Gold Crown
    edited Feb 3, 2020 12:36PM

    Something doesn't make sense to me from the outset. Why do you need the output in that format to begin with?

    It seems to me that you are trying to classify each "description" as a unique "category"; that is, each row will have the flag = 1 in exactly one column, while all the other flags will be 0. Is that correct? So, a "description" can either be Women's Apparel or Women's Swim, but never BOTH - right?

    If so, why not just create an output that has two columns: "category_description" and "classification"?  (I would call the latter "category", but your "category description" does not describe "Men's Accessories"; it is best if the column names don't create a false impression that the "description" belongs to the "classification", in my terminology.)

    So, then, you would have an output like this:

    CATEGORY_DESCRIPTION                                  CLASSIFICATION----------------------------------------------------  ---------------------Baby > Basics > Accessories > Hats,Scarves, & Gloves  Baby ProductsMen > Shoes & Accessories > Hats                      Men's Accessoriesetc.

    The valid "classifications" should be stored and maintained in a small table, by themselves. The results of this query should be stored and maintained in a somewhat bigger table, and the CLASSIFICATION column should reference the small table. ** Then your products table should reference the CATEGORY_DESCRIPTION column. You could create the table with the mappings from "description" to "classification" by any means necessary - for example, with your approach, plus manual corrections where needed. (Like Paulzip, I would not rely on any fully-automated solution alone - there are way too many possible exceptions and possible traps to have any hope to foresee all of them.) And then, if you really need a result in the format you are showing, with 0/1 flags in many columns, you could do that trivially with a PIVOT operation.

    ** In a proper design, the "classifications" table would have two columns (and perhaps others); a primary key, best populated as an IDENTITY column, and the description in words, like 'Baby Products'. The mapping table should have three columns (and perhaps others) - a primary key (for category descriptions this time!), best populated as an IDENTITY column, and the two columns I show above. And your individual products table should not reference the CATEGORY_DESCRIPTION column, but the CATEGORY_ID column (if that's what you will call the PK).

Sign In or Register to comment.