Search and extract specific substring in table transformer where string is separated by spaces

Preeti Bhole

I have a column with a huge string where words are separated by spaces and withing words there are hyphens

ADA Applause Applause-Cycle-476297 Applause_Man CEACCESS-3677 Project-glass WCAG-1.3.2 WCAG-A applause-wcp-ca-Home-Departments applause_accessibility_cycle glass-web-e2e wcp-e2e-ca

I want to extract below 3 things out of above string:

1. Home-Departments --> it is part of applause-wcp-ca-Home-Departments substring
2. web --> it is part of glass-web-e2e substring
3. 1.3.2 --> it is part of WCAG-1.3.2 substring

I tried some workaround but not getting exactly what I want with this as the length of the strings that i want to extract can change

SELECT

CASE WHEN T1.'Labels'->split(" ") like ("%glass-%") THEN 

SUBSTRING(T1.'Labels'->split("glass-")->1, 1, 4)

END as 'Platform',

CASE WHEN T1.'Labels'->split(" ") like ("%WCAG-%") THEN 

SUBSTRING(T1.'Labels'->split("WCAG-")->1, 1, 6)

END as 'WCAG Criteria',

CASE WHEN T1.'Labels'->split(" ") like ("%applause-wcp-ca-%") THEN 

SUBSTRING(T1.'Labels'->split("applause-wcp-ca-")->1, 1, 16)

END as 'Feature',

FROM T1

Preeti Bhole

I was able to resolve myself with below query:

SELECT CASE WHEN T1.'Labels'->split(" ") like ("%glass-%") THEN
(T1.'Labels'->split("glass-")->1)->split("-")->0
END as 'Platform',
CASE WHEN T1.'Labels'->split(" ") like ("%WCAG-%") THEN
SUBSTRING(T1.'Labels'->split("WCAG-")->1, 1, 6)
END as 'WCAG Criteria',
CASE WHEN T1.'Labels'->split(" ") like ("%applause-wcp-ca-%") THEN
(T1.'Labels'->split("applause-wcp-ca-")->1)->split(" ")->0
END as 'Feature'
FROM T1