英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论