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
Next: Root
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.