英文:
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 LoweredTextFROMsc_join_group)SELECT*,SUBSTRING(LoweredText, 1, PATINDEX('%[A-Z]%', LoweredText) - 1) AS leftcol,SUBSTRING(LoweredText, PATINDEX('%[A-Z]%', LoweredText), LEN(LoweredText)) AS rightrightFROMunique_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 LoweredTextFROMsc_join_group)SELECT*,SUBSTRING(LoweredText, 1, PATINDEX('%[A-Z]%', LoweredText) - 1) AS leftcol,SUBSTRING(LoweredText, PATINDEX('%[A-Z]%', LoweredText), LEN(LoweredText)) AS rightrightFROMunique_text;
答案1
得分: 1
以下是翻译好的内容：
这里是一个你可以尝试的替代版本，利用translate在区分大小写的排序规则上识别要拆分的字符：
select Textcolumn,IsNull(Left(TextColumn, p - 1), Textcolumn) Leftcol,Right(TextColumn, Len(TextColumn) - p + 1) rightcolfrom tcross 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) rightcolfrom tcross 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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论