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 · Accepted solution]

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

[Stiltsoft support]

Hi @Preeti Bhole ,

Seems that you are talking about our app - Table Filter, Charts & Spreadsheets for Confluence.

Here, in the Community, we try to find related questions manually and may miss smth, so if you have any questions regarding our macros, please refer to our support portal. 

The portal is confidential, so you'll be able to share your data and we'll try to help with the cases.

[Preeti Bhole]

Sounds good, thank you for sharing