Regex is not marking the correct tag

Rohit Jha 0
Viewed 38
case when regexp_like(
    lower(ht_s), 
    '.*wind.?(6\.1|7|8|10|11|.*(ce|vi|xp).*)|.*work.*|.*mc.*'
  ) then 'Cli'
  when regexp_like(
    lower(ht_s), 
    '.*(arise
     |fevax|vm[ww]are|wind).*'
  ) then 'Ser'
    ELSE 'Others' END AS "Dev Ty"

This regex is giving me the correct result but wind.ce is suppose to be "cli" which is giving me correct result but "wind center" is suppose to be marked as "Ser" It is getting marked as "cli" I dont know where I am doing something wrong ,The main thing which getting me this is marked as bold

data example with expected output

                dev ty(expected ouput)  coming Output
Wind datacenter Ser                     Cli
wind ce 5.x     Cli                     Cli
snowflake-schema snowflake-cloud-data-platform
1 Answers

So if we break out those two regexp into there own columns, so we can separate the CASE from the REGEXP:

select 
    column1 as ht_s,
    regexp_like( lower(ht_s), '.*wind.?(6\.1|7|8|10|11|.*(ce|vi|xp).*)|.*work.*|.*mc.*' ) as r1,
    regexp_like( lower(ht_s), '.*(arise|fevax|vm[ww]are|wind).*' ) as r2,
    case 
        when r1 then 'Cli'
        when r2 then 'Ser'
        else 'Others' 
    end as "Dev Ty",
from values
    ('Wind datacenter'),
    ('wind ce 5.x');
HT_S R1 R2 Dev Ty
Wind datacenter TRUE TRUE Cli
wind ce 5.x TRUE TRUE Cli

So just as Phil notes, this is just a REGEXP problem, and you need to improve you first regexp, thus we can ignore the CASE part.

For the data you have presented, the first regex '.*wind.?(6\.1|7|8|10|11|.*(ce|vi|xp).*)|.*work.*|.*mc.*' can be trimmed down to the parts that are in affect: '.*wind.*ce.*'

select 
    column1 as ht_s
    ,regexp_like( lower(ht_s), '.*wind.?(6\.1|7|8|10|11|.*(ce|vi|xp).*)|.*work.*|.*mc.*' ) as r1
    ,regexp_like( lower(ht_s), '.*wind.*ce.*' ) as r2
from values
    ('Wind datacenter'),
    ('Wind center'),
    ('wind ce 5.x');

which is matches anything with a ce in it after wind

HT_S R1 R2
Wind datacenter TRUE TRUE
Wind center TRUE TRUE
wind ce 5.x TRUE TRUE

thus changing the "white space matches that you appear to be any the any token . and using the whitespace /s which needs to be escaped to //s

'.wind\s?ce\s.' only matches wind ce and not the others.

so something like:

select 
    column1 as ht_s
    ,regexp_like( lower(ht_s), '.*wind.?(6\.1|7|8|10|11|.*(ce|vi|xp).*)|.*work.*|.*mc.*' ) as r1
    ,regexp_like( lower(ht_s), '.*wind\\s?(6\.1|7|8|10|11|\\s*(ce|vi|xp))\\s+.*' ) as r1_fix
    ,regexp_like( lower(ht_s), '.*wind.*ce.*' ) as r2
    ,regexp_like( lower(ht_s), '.*wind.*(ce ).*' ) as r3
    ,regexp_like( lower(ht_s), '.*wind\\s?ce\\s.*' ) as r4
from values
    ('Wind 6.1'),
    ('Wind 7'),
    ('Wind xp'),
    ('Wind datacenter'),
    ('Wind center'),
    ('wind ce 5.x');
HT_S R1 R1_FIX R2 R3 R4
Wind 6.1 TRUE FALSE FALSE FALSE FALSE
Wind 7 TRUE FALSE FALSE FALSE FALSE
Wind xp TRUE FALSE FALSE FALSE FALSE
Wind datacenter TRUE FALSE TRUE FALSE FALSE
Wind center TRUE FALSE TRUE FALSE FALSE
wind ce 5.x TRUE TRUE TRUE TRUE TRUE
Simeon Pilgrim0
Related