how to return a unique set of characters within a field (SQL)?

Basically i am trying to return any fields within the tag column that contain up_ up1_ up2_ up3, up4_.

the problem i’m facing is i only want to show these tags and not the others.

The field contains a mixture of characters on each row so i’m finding it difficult to use a CASE statement substr.

I’ve been able to return the entire field using this sql:

select tags
from products
where tags like '%up_%'
   or tags like '%Up*_%'

any ideas?

Many thanks, Jonathon

EDIT data within the column:

almost gone, 
Autumn/Winter, 
BEIGE
Coats Jackets
Cream, Gender_Female
ISAWITFIRST.com 
Neutrals 
Pcat-Coats & Jackets
S
Sale
SALE:New In Coats & Jackets
Sale:SALE:New In Clothing
Scat-Coat 
Up1_Cup-Detail-Sheer-Panel-Bodysuit-Cream-Jl33334
Up_High-Waisted-Skinny-Trousers-White-Jl34117

I want to be able to return only the UP1_ and Up_ tags. and want to remove the rest..

EDIT 2

This column is populated by an external site

Answer

For PostgreSQL for instance, you could go with the following:

select substring(tags from '[Uu]p[0-9]?_')
from products
where tags like '%up_%'
   or tags like '%Up*_%'

Please adjust the regular expression in the first line to your exact needs.

For other DB-Systems, swap the substring function for one of the following:

For TSQL, have a look at the “patindex”-function. For Athena, consider the REGEXP_SUBSTR function, as described here: https://docs.aws.amazon.com/redshift/latest/dg/REGEXP_SUBSTR.html