如何在SQL Server中按第二个大写字符拆分列

huangapple go评论83阅读模式
英文:

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

问题

我在SQL Server中有一个表,其中有一个列的内容如下。

Textcolumn
SzhH
Tma
SzhH
CcMtp
XYZ

我想将Textcolumn拆分为两列,如下所示(以大写字母为分隔符)。

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

我尝试了以下代码,但没有成功。请帮助我!

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;
英文:

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;

答案1

得分: 1

以下是翻译好的内容:

这里是一个你可以尝试的替代版本,利用translate在区分大小写的排序规则上识别要拆分的字符:

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)

请参考这个demo Fiddle

英文:

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

huangapple
  • 本文由 发表于 2023年8月9日 16:58:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/76866110.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定