use tempdb go if object_id('tb') is not null drop table tb if object_id('#T') is not null drop table #T go create table tb(col1 varchar(max)) insert into tb select 'abc' union all select 'aabc' union all select 'abcCC' union all select 'abbc' union all select 'abc cde' go ;WITH MU AS ( SELECT ROW_NUMBER() OVER(ORDER BY T1.NUMBER) AS NUMBER FROM MASTER..SPT_VALUES T1 INNER JOIN MASTER..spt_values T2 ON 1=1 ),MU2 AS ( SELECT NUMBER FROM MU WHERE NUMBER<=(SELECT MAX(LEN(COL1)) FROM TB) ) SELECT * INTO #T FROM MU2 GO SELECT T1.col1 FROM TB T1 INNER JOIN #T T2 ON LEN(T1.COL1)>0 AND LEN(T1.COL1)>=T2.NUMBER+1 INNER JOIN #T T3 ON SUBSTRING(T1.COL1,T2.NUMBER,1)=SUBSTRING(T1.COL1,T3.NUMBER,1) AND T2.NUMBER=T3.NUMBER-1 AND LEN(T1.COL1)>=T3.NUMBER GROUP BY T1.col1 /* aabc abbc abcCC */
go
if object_id('tb') is not null drop table tb
if object_id('#T') is not null drop table #T
go
create table tb(col1 varchar(max))
insert into tb
select 'abc' union all
select 'aabc' union all
select 'abcCC' union all
select 'abbc' union all
select 'abc cde'
go
;WITH MU AS (
SELECT ROW_NUMBER() OVER(ORDER BY T1.NUMBER) AS NUMBER
FROM MASTER..SPT_VALUES T1
INNER JOIN MASTER..spt_values T2 ON 1=1
),MU2 AS (
SELECT NUMBER
FROM MU
WHERE NUMBER<=(SELECT MAX(LEN(COL1)) FROM TB)
)
SELECT *
INTO #T
FROM MU2
GO
SELECT T1.col1
FROM TB T1
INNER JOIN #T T2 ON LEN(T1.COL1)>0 AND LEN(T1.COL1)>=T2.NUMBER+1
INNER JOIN #T T3 ON SUBSTRING(T1.COL1,T2.NUMBER,1)=SUBSTRING(T1.COL1,T3.NUMBER,1) AND T2.NUMBER=T3.NUMBER-1 AND LEN(T1.COL1)>=T3.NUMBER
GROUP BY T1.col1
/*
aabc
abbc
abcCC
*/