[SQL]取出某字在字串之間的字串

2014-06-13

3474
0
SQL

如果要找出這些字之間的字串是什麼，要如何做呢?

在「找出某個字在字串中出現的次數」中我們可找出某個字出現在字串中的次數。

那如果要找出這些字之間的字串是什麼，要如何做呢?

我們可以先透過 CTE (common_table_expression) 用 charindex 將字串兩兩位置找出來，然後再透過 SubString 來取出來即可，如下，

USE tempdb
GO

IF OBJECT_ID('dbo.t1') IS NOT NULL
	DROP TABLE t1;

CREATE TABLE t1
(
c1 VARCHAR(4000)
);
GO

INSERT INTO t1(c1) VALUES('1.11111111111
2...........
3.XXXXXXXXX
4.YYYYYYYYY
5.DDDDDDDDDDD
char(10)大於3個
');

INSERT INTO t1(c1) VALUES('沒char(10)');

INSERT INTO t1(c1) VALUES('1.11111111111
2...........
3.XXXXXXXXXchar(10) 2個');

INSERT INTO t1(c1) VALUES('1.11111111111
2...........
3.XXXXXXXXX
4...
char(10) 4個');


DECLARE @search CHAR(1)
SET @search = CHAR(10)
;with cte_nth as (
    select c1, 1 AS starts , charindex(@search, c1) AS pos, 1 AS idx from t1
    union all
    select c1, pos + 1, charindex(@search, c1, pos + 1), idx + 1
    from cte_nth
    where pos > 0
)
select 
    *, substring(c1, starts, case when pos > 0 then pos - starts else len(c1) end) token
from cte_nth
order by c1, starts;