You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
I am trying to create columns out of a single text multiline column using SQL
Priority | Medium
Project | Proj1
Category | Cat1
Three columns: Priority, Project, Category
I know how to write a case statement easily
Case when Desc [condition] then end 'Priority',
Case when Desc [condition] then end 'Project',
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
Hi @MOh G,
You can try to use the SUBSTRING funtion. Please see the example below:
SUBSTRING(T1.'column 1',11,7) AS 'Priority',
SUBSTRING(T1.'column 1',28,5) AS 'Project',
SUBSTRING(T1.'column 1',44,4) AS 'Category'
Hi @MOh G,
Then you need more complex SQL query like this (please see the example below):
TT1.'column 2'->match("[$]P:(.+?)[$]")-> AS 'Priority',
TT1.'column 2'->match("[$]Pr:(.+?)[$]")-> AS 'Project',
TT1.'column 2'->match("[$]C:(.+?)[$]")-> AS 'Category'
T1.'column 1'->replaceAll("Priority | ", "$P:")
->replaceAll("Project | ", "$Pr:")
->replaceAll("Category | ", "$C:")
->replaceAll("Another | ", "$")
AS 'column 2'
FROM T1) AS TT1