How to split a column by the second upper character in SQL Server

Question

I got a table in SQL Server with the one column like this.

Textcolumn
SzhH
Tma
SzhH
CcMtp
XYZ

I want to split the Textcolumn into 2 columns like this (with the delimiter is the uppercase character)

Textcolumn	leftcol	rightcol
SzhH	Szh	H
Tma	Tma	Null
SzhH	Szh	H
CcMtp	Cc	Mtp
XYZ	XYZ	Null

I have tried this. But it did not work. Please help me!

WITH unique_text AS
(
    SELECT 
        *, 
        LOWER(SUBSTRING([TextColumn], 1, 1)) + SUBSTRING([TextColumn], 2, LEN([TextColumn])) AS LoweredText
    FROM 
        sc_join_group
)
SELECT 
    *,
    SUBSTRING(LoweredText, 1, PATINDEX('%[A-Z]%', LoweredText) - 1) AS leftcol,
    SUBSTRING(LoweredText, PATINDEX('%[A-Z]%', LoweredText), LEN(LoweredText)) AS rightright
FROM
    unique_text;

Answer 1

Here's an alternative version you can try, making use of translate on a case-sensitive collation to identify the character to split on:

select Textcolumn, 
  IsNull(Left(TextColumn, p - 1), Textcolumn) Leftcol, 
  Right(TextColumn, Len(TextColumn) - p + 1) rightcol
from t
cross apply(values(Translate(Textcolumn collate SQL_Latin1_General_CP1_CS_AS, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', Replicate('*',26))))n(t)
cross apply(values(NullIf(Iif(Replace(n.t,'*','') = '', 0, CharIndex('*', n.t, 2)), 0)))p(p)

See this demo Fiddle