T-sql CTE recursion

I just want it to return 1 2

WITH 
CTE1 AS
(
    select value
    UNION ALL
    select value=v开发者_开发问答alue+1
    FROM CTe1
    WHERE value =2
)

select * from cte1

how come that doesnt work.

The following will print out 1, 2:

WITH 
CTE1 AS
(
    select 1 as value
    UNION ALL
    select value=value+1
    FROM CTe1
    WHERE value = 1
)

select * from cte1

The problem was that value was not defined for your first CTE clause. I assume you wanted 1. Then the second CTE clause self-references the first one and adds 1.

The CTE will print 1 and 2 as rows. Is this what you are after?

WITH 
CTE1 AS
(
    select 1 as value
    UNION ALL
    select value=value+1
    FROM CTe1
    WHERE value < 2
)
select * from cte1

here is a code to search a string for a character macth thank you all.

Begin
with recursiveCTE(matchNumber, foundAt) as (
  select 1, charindex(@toFind, @ToSearch, 0)
  union all
  select matchNumber + 1, charindex(@toFind, @ToSearch, foundAt + 1)
  from recursiveCTE where foundAt > 0
)
select
  matchNumber as "Match Number",
  (case when foundAt = 0 then null else foundAt end) as "Found At"
from recursiveCTE
where  foundAt  0 or matchNumber = 1
;

end;