Combining multiple regexp_like statements & correct regex syntax

4169408 · Feb 2, 2020 6:49PM

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.

For each category flag variable combine the two regexp_like statements into only one regexp_like statement
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:

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

Current script:

select

<td class="xl66" style="

category_description	Mens_Apparel	Womens_Apprarel	Mens_Accessories	Womens_Accessories	Mens_Swim	Womens_Swim	Kids_Products	Baby_Products
Baby > Basics > Accessories > Hats,Scarves, & Gloves	0	0	0	0	0	0	0	1
Men > Shoes & Accessories > Hats	0	0	1	0	0	0	0	0
Men > Shoes & Accessories > Ties	0	0	1	0	0	0	0	0
Women > Clothing > Jackets	0	1	0	0	0	0	0	0
Women > Shoes & Accessories > Hats	0	0	0	1	0	0	0	0
Kids > Boys > Jackets	0	0	0	0	0	0	1	0
Tall/Men/Outerwear/Cloth/Sale	1	0	0	0	0	0	0	0
Men > Bottoms > Swim	1	0	0	0	1	0	0	0
Men > Bottoms > Swimwear	1	0	0	0	1	0	0	0
US/Big_Tall/Men/Chinos/Flat_Front	1	0	0	0	0	0	0	0
men>clothing>sweatshirts	1	0	0	0	0	0	0	0
men>clothing>swimwear	1	0	0	0	1	0	0	0
Children/Girls/Outfit	0	0	0	0	0	0	1	0
Men > Accessories > Fragrance	0	0	1	0	0	0	0	0
Black_Top/Men/Dress_Shirts/Slim_Fit/Sale	1	0	0	0	0

Frank Kulash · Feb 2, 2020 9:11PM

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 · Feb 2, 2020 9:47PM

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

Paulzip · 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 · 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).

Forum Stats

Howdy, Stranger!

Quick Links

Categories

Combining multiple regexp_like statements & correct regex syntax

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Howdy, Stranger!

Categories

Combining multiple regexp_like statements & correct regex syntax

Answers