REGEX Exclude First Dash

Question

I am attempting to use the REGEXP_MATCH, REGEXP_EXTRACT functions in tableau to isolate 'designated port' from a data set.

I am having a horrible time with the entries that have a leading '-', I have tried the following and the best I can get is the 4 digit with the leading "-", which I do not want. I want it to return digit-digit with as many digits as meets that criteria.

first solution:

(\d*-\d*)*

second solution:

(\d*-(\d*-\d*-\d*-\d*))

third solution (only returns on '1-1-1-1' format, matches as -1-1-1-1):

([0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3})

Data sample:

NETWORK-1-1-1-1
ACCESS-1-1-1-1
FA0/1
GE-0/2/3
XE-2/1/10
GI0/0/1
GIGABIT ETHERNET 1-1-2
1-1-2-100
1-1-2-10.943

Desired Isolation (In Bold)

NETWORK-1-1-1-1
ACCESS-1-1-1-1
FA0/1
GE-0/2/3
XE-2/1/10
GI0/0/1
GIGABIT ETHERNET 1-1-2
1-1-2-100
1-1-2-10.943

Desired Return

1-1-1-1
1-1-1-1
excluded
excluded
excluded
excluded
1-1-2
1-1-2-100
1-1-2-10

Tableau Formula with Regex match and extract:

IF REGEXP_MATCH([Port Access Less VLAN],'(\d{0,3}\/\d{0,3}\/\d{0,3})')
THEN REGEXP_EXTRACT([Port Access Less VLAN],'(\d{0,3}\/\d{0,3}\/\d{0,3})')

ELSEIF REGEXP_MATCH([Port Access Less VLAN],'(\d{0,3}\/\d{0,3})')
THEN REGEXP_EXTRACT([Port Access Less VLAN],'(\d{0,3}\/\d{0,3})')

ELSEIF REGEXP_MATCH([Port Access Less VLAN],'([0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3})')
THEN REGEXP_EXTRACT([Port Access Less VLAN],'([0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3})')

ELSE NULL
END

Tableau Results:

1-1-1-1
1-1-1-1
0/1
0/2/3
2/1/10
0/0/1
1-1-2
1-1-2-100
1-1-2-10

Answer 1

got a solution through DM from Wiktor, works beautifully

(\d+(-\d+)+)

previous code

IF REGEXP_MATCH([Port Access Less VLAN],'(\d{0,3}\/\d{0,3}\/\d{0,3})')
THEN REGEXP_EXTRACT([Port Access Less VLAN],'(\d{0,3}\/\d{0,3}\/\d{0,3})')

ELSEIF REGEXP_MATCH([Port Access Less VLAN],'(\d{0,3}\/\d{0,3})')
THEN REGEXP_EXTRACT([Port Access Less VLAN],'(\d{0,3}\/\d{0,3})')


ELSEIF REGEXP_MATCH([Port Access Less VLAN],'([0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3})')
THEN REGEXP_EXTRACT([Port Access Less VLAN],'([0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3})')

ELSEIF REGEXP_MATCH([Port Access Less VLAN],'([0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3})')
THEN REGEXP_EXTRACT([Port Access Less VLAN],'([0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3})')

ELSEIF REGEXP_MATCH([Port Access Less VLAN],'([0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3})')
THEN REGEXP_EXTRACT([Port Access Less VLAN],'([0-9]{0,3}-[0-9]{0,3}-[0-9]{0,3})')

ELSEIF REGEXP_MATCH([Port Access Less VLAN],'([0-9]{0,3}-[0-9]{0,3})')
THEN REGEXP_EXTRACT([Port Access Less VLAN],'([0-9]{0,3}-[0-9]{0,3})')

ELSEIF REGEXP_MATCH([Port Access Less VLAN],'(-[0-9]{0,3})')
THEN REGEXP_EXTRACT([Port Access Less VLAN],'(-[0-9]{0,3})')

ELSE NULL
END

New Improved and WORKING code

IF REGEXP_MATCH([Port Access Less VLAN],'(\d+(\/\d+)+)')
THEN REGEXP_EXTRACT([Port Access Less VLAN],'(\d+(\/\d+)+)')

ELSEIF REGEXP_MATCH([Port Access Less VLAN],'(\d+(-\d+)+)')
THEN REGEXP_EXTRACT([Port Access Less VLAN],'(\d+(-\d+)+)')

ELSE NULL
END

Answer 2

You should use {1,3} instead of {0,3}.