英文:
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 rightcol
FROM
unique_text;
请注意，我将 rightright 改为了 rightcol，因为这似乎是您想要的列名。
英文:
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)
请参阅此演示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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论