英文:
Get the text between two "-"s for each row in SQL Server
问题
请帮助我在SQL Server中剪裁下面的示例文本。我想要获取两个 '-' 之间的数据（某些行没有 '-'）。
期望的输出是：
AB DCE
AB DCE
ABC
AB DCE
ABC
我尝试了：
select substring(mycol, 4, charindex('-', mycol, 12))
但结果不准确。
英文:
Can someone please help me with trimming the below sample texts in SQL Server? I am looking to get the data between the two '-' (some rows do not have a '-').
AB DCE
CM-AB DCE -228
ABC
CM-AB DCE-214
CJ-ABC-228
The output expected is:
AB DCE
AB DCE
ABC
AB DCE
ABC
I tried:
select substring(mycol, 4, charindex('-', mycol, 12) )
But the results are not accurate.
答案1
得分: 0
以下是翻译好的部分：
如果您使用 CHARINDEX 收集第一个符号的位置和第二个符号的位置，您可以使用 SUBSTRING 提取它们之间的内容。
WITH cte AS (
SELECT *,
CHARINDEX('-', txt) AS startidx,
CHARINDEX('-', txt, CHARINDEX('-', txt)+1) AS endidx
FROM tab
)
SELECT SUBSTRING(txt,
CASE WHEN startidx > 0 THEN startidx+1 ELSE 0 END,
CASE WHEN startidx > 0 THEN endidx-startidx-1 ELSE LEN(txt) END) AS txt
FROM cte
输出：
| txt |
|---|
| AB DC |
| AB DCE |
| AB |
| AB DCE |
| ABC |
在此查看演示链接。
英文:
If you gather the position of your first symbol, and the position of your second symbol with CHARINDEX, you can extract what's contained inbetween with SUBSTRING.
WITH cte AS (
SELECT *,
CHARINDEX('-', txt) AS startidx,
CHARINDEX('-', txt, CHARINDEX('-', txt)+1) AS endidx
FROM tab
)
SELECT SUBSTRING(txt,
CASE WHEN startidx > 0 THEN startidx+1 ELSE 0 END,
CASE WHEN startidx > 0 THEN endidx-startidx-1 ELSE LEN(txt) END) AS txt
FROM cte
Output:
| txt |
|---|
| AB DC |
| AB DCE |
| AB |
| AB DCE |
| ABC |
Check the demo here.
答案2
得分: 0
以下是您要翻译的内容：
"seems like the question has been answered here
https://www.webcodeexpert.com/2016/08/sql-server-query-to-get-string-between.html
SELECT SUBSTRING(txt,CHARINDEX('-',txt)+1,
(((LEN(txt))-CHARINDEX('-',REVERSE(txt)))-CHARINDEX('-',txt)))
AS Result FROM tab"
英文:
seems like the question has been answered here
https://www.webcodeexpert.com/2016/08/sql-server-query-to-get-string-between.html
SELECT SUBSTRING(txt,CHARINDEX('-',txt)+1,
(((LEN(txt))-CHARINDEX('-',REVERSE(txt)))-CHARINDEX('-',txt)))
AS Result FROM tab
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论