Table Transformer - Substring based on Expression

[MOh G]

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

[Katerina Rudkovskaya _Stiltsoft_ · Accepted solution]

Hello @MOh G

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



Thanks!

Katerina

[MOh G]

Hi Katrina

Yes, this is the desired result

[MOh G]

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

[Katerina Rudkovskaya _Stiltsoft_]

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

[MOh G]

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

[Katerina Rudkovskaya _Stiltsoft_]

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