The Atlassian Community can help you and your team get more value out of Atlassian products and practices.
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
Hello @MOh G
Could you please confirm if your use case is like this (see the screenshot below)?
Thanks!
Katerina
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Katerina Rudkovskaya _Stiltsoft_ I used Split to separate lines then another split to use | symbol. Somehow the newline split is not working
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @MOh G,
You can try to use the SUBSTRING funtion. Please see the example below:
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*
BR,
Katerina
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Katerina Rudkovskaya _Stiltsoft_ the source string is rarely, if ever, fixed. a regular expression is needed here
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Katerina
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
👋 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 ...
Connect with like-minded Atlassian users at free events near you!
Find an eventConnect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.
Host an eventYou're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.