Get the text between two "-"s for each row in SQL Server

dauxcl2d 于 2023-05-28 发布在 SQL Server

关注(0)|答案(2)|浏览(155)

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.

sql-server

来源：https://stackoverflow.com/questions/76324037/get-the-text-between-two-s-for-each-row-in-sql-server

2条答案

按热度按时间

1yjd4xko1#

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 .

展开查看全部

赞(0）回复(0）举报 2023-05-28

wz8daaqr2#

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

赞(0）回复(0）举报 2023-05-28

我来回答

Get the text between two "-"s for each row in SQL Server

2条答案

相关问题

热门标签

最新问答