Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

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,463,739
Community Members
 
Community Events
176
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

2 votes
Answer 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

Like # people like this

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events