Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,295,503
Community Members
 
Community Events
165
Community Groups

Table Transformer - Substring based on Expression

I am trying to create columns out of a single text multiline column using SQL

 

Data

-----

Priority | Medium
Project | Proj1
Category | Cat1

 

Desired Result

Three columns: Priority, Project, Category

 

I know how to write a case statement easily

select 

Case when Desc  [condition]  then end 'Priority',

Case when Desc  [condition]  then end 'Project',

..etc

 

Now to extract values after | , I could write the a Java script or T-SQL that uses a substring and a charindex to locate text between | and newline.

 

Any help with transformer SQL? It should be easy but my luck is not hitting for now

 

 

1 answer

1 accepted

Hello @MOh G

Could you please confirm if your use case is like this (see the screenshot below)?

2022-04-30_00h09_25.png

Thanks!

Katerina

Hi Katrina

 

Yes, this is the desired result

@Katerina Rudkovskaya _Stiltsoft_  I used Split to separate lines then another split to use | symbol. Somehow the newline split is not working

Hi @MOh G,

You can try to use the SUBSTRING funtion. Please see the example below:

2022-05-07_00h03_29.png

SELECT
SUBSTRING(T1.'column 1',11,7) AS 'Priority',
SUBSTRING(T1.'column 1',28,5) AS 'Project',
SUBSTRING(T1.'column 1',44,4) AS 'Category'
FROM T*

2022-05-07_00h03_33.png2022-05-07_00h03_36.png

BR,
Katerina

@Katerina Rudkovskaya _Stiltsoft_ the source string is rarely, if ever, fixed. a regular expression is needed here

Hi @MOh G,

Then you need more complex SQL query like this (please see the example below):

SELECT
TT1.'column 2'->match("[$]P:(.+?)[$]")->[1] AS 'Priority',
TT1.'column 2'->match("[$]Pr:(.+?)[$]")->[1] AS 'Project',
TT1.'column 2'->match("[$]C:(.+?)[$]")->[1] AS 'Category'
FROM (SELECT
T1.'column 1'->replaceAll("Priority | ", "$P:")
->replaceAll("Project | ", "$Pr:")
->replaceAll("Category | ", "$C:")
->replaceAll("Another | ", "$")
+ "$"
AS 'column 2'
FROM T1) AS TT1

2022-05-10_14h24_52.png2022-05-10_14h25_14.png2022-05-10_14h25_16.png

Katerina

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence Cloud

Presenter Mode for Confluence is here 🖥 👩‍💻

👋  Hello Community!  My name is Stephanie Zhang, and I’m a product manager on the Confluence cloud team. Today, I’m excited to announce the rollout of  Presenter Mode : a ...

80,213 views 126 304
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you