SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t 
(
    rn integer IDENTITY (1, 1) NOT NULL,
    a varchar(100) NOT NULL
);
INSERT INTO t VALUES ('|3|3| some stuff here');
1 rows affected

INSERT INTO t VALUES ('|SE + 18.5D  some other stuff');
1 rows affected

INSERT INTO t VALUES ('|54|45| yet more stuff');
1 rows affected

INSERT INTO t VALUES ('|32|23| yet more |78|78| stuff');
1 rows affected

INSERT INTO t VALUES ('|This is more text |11|111|22222||| and stuff |||||||');
1 rows affected

INSERT INTO t VALUES ('This is more text |11|111|22222||| and stuff |||||||');
1 rows affected

INSERT INTO t VALUES ('|text|123|456|');
1 rows affected

SELECT * FROM t;


rn
a


1
|3|3| some stuff here

2
|SE + 18.5D  some other stuff

3
|54|45| yet more stuff

4
|32|23| yet more |78|78| stuff

5
|This is more text |11|111|22222||| and stuff |||||||

6
This is more text |11|111|22222||| and stuff |||||||

7
|text|123|456|


SELECT
    P.rn,
    P.a,
    b = P.[1],
    c = P.[2]
FROM
(
    SELECT
        t3.a,
        t3.rn,
        t3.i,
        t3.irn
    FROM
    (
        SELECT
            t2.a,
            t2.rn,
            t2.i,
            t2.srn,
            irn = SUM(IIF(t2.i IS NOT NULL, 1, 0)) OVER (
                PARTITION BY t2.rn 
                ORDER BY @@SPID 
                ROWS UNBOUNDED PRECEDING)
        FROM
        (
            SELECT
                t.a,
                t.rn, 
                i = TRY_PARSE(SS.value AS integer),
                srn = ROW_NUMBER() OVER (
                    PARTITION BY t.rn 
                    ORDER BY t.rn)
            FROM t
            CROSS APPLY STRING_SPLIT(TRIM('|' FROM t.a), '|') AS SS
        ) AS t2
        WHERE
            t2.srn IN (1, 2)
    ) AS t3
    WHERE
        t3.irn IN (0, t3.srn)
) AS t4
PIVOT
(
    MAX(t4.i)
    FOR t4.irn IN ([1], [2])
) AS P
ORDER BY
    P.rn;


rn
a
b
c


1
|3|3| some stuff here
3
3

2
|SE + 18.5D  some other stuff
null
null

3
|54|45| yet more stuff
54
45

4
|32|23| yet more |78|78| stuff
32
23

5
|This is more text |11|111|22222||| and stuff |||||||
null
null

6
This is more text |11|111|22222||| and stuff |||||||
null
null

7
|text|123|456|
null
null

rn	a
1	\|3\|3\| some stuff here
2	\|SE + 18.5D some other stuff
3	\|54\|45\| yet more stuff
4	\|32\|23\| yet more \|78\|78\| stuff
5	\|This is more text \|11\|111\|22222\|\|\| and stuff \|\|\|\|\|\|\|
6	This is more text \|11\|111\|22222\|\|\| and stuff \|\|\|\|\|\|\|
7	\|text\|123\|456\|

rn	a	b	c
1	\|3\|3\| some stuff here	3	3
2	\|SE + 18.5D some other stuff	null	null
3	\|54\|45\| yet more stuff	54	45
4	\|32\|23\| yet more \|78\|78\| stuff	32	23
5	\|This is more text \|11\|111\|22222\|\|\| and stuff \|\|\|\|\|\|\|	null	null
6	This is more text \|11\|111\|22222\|\|\| and stuff \|\|\|\|\|\|\|	null	null
7	\|text\|123\|456\|	null	null